Using SQL-NS to Invoke SQL Server Enterprise Manager Components

The main SQL-NS interface is the SQLNamespace object, which enumerates the objects in the namespace. Individual objects are identified by the HSQLNSITEM handle. A SQLNamespaceObject interface can be obtained for this handle by calling the GetSQLNamespaceObject method.

The steps to invoke a user interface component are:

1. Create the SQLNamespace object.

   Visual Basic

   Dim oSQLNS As SQLNamespace
   Set oSQLNS = New SQLNamespace 
   ' or using late binding
   Dim oSQLNS As Object
   Set oSQLNS = CreateObject("SQLNS.SQLNamespace") 
   

   Visual C++

   ISQLNamespace * pNS = NULL;
   CoInitialize(NULL);
   CoCreateInstance(CLSID_SQLNamespace, NULL, LSCTX_INPROC_SERVER,      IID_ISQLNamespace, (LPVOID*)&pNS);
   

2. Call ISQLNamespace::Initialize to initialize the namespace. This is the first action required after the object is declared, and it only needs to be called once for the lifetime of the SQLNamespace object. The namespace can be initialized at one of four different starting points (SQLNSRootType) in the namespace tree. The four SQLNSRootType constants are:
   • SQLNSRootType_DefaultRoot
     
     
   • SQLNSRootType_ServerGroup
     
     
   • SQLNSRootType_Server
     
     
   • SQLNSRootType_Database

   The first two constants, SQLNSRootType_DefaultRoot and SQLNSRootType_ServerGroup, retrieve their connection information from the registration information of SQL Server Enterprise Manager. Because this depends on a user's individual configuration, it is not recommended that an application use these two SQLNSRootType constants as a starting point. Instead, an application should use SQLNSRootType_Server, or SQLNSRootType_Database, because they give you complete control over the destination without having to depend on an individual user's SQL Server Enterprise Manager configuration.

3. Pass a connection string as a parameter for the Initialize method. The connection string is almost identical to an ODBC connection string. The parts of the connection string are:
   • Server=MyServer;
     
     
   • SrvGrp=SQL Server Group;
     
     
   • UID=sa;
     
     
   • PWD=;
     
     
   • Trusted_Connection=Yes;
     
     
   • Database=pubs;

   If ROOTTYPE is SQLNSRootType_DefaultRoot, no connection string is required because all necessary information is retrieved from the SQL Server Enterprise Manager registration information on the local computer. If ROOTTYPE is SQLNSRootType_ServerGroup, the connection string only needs to contain the SvrGrp=SQL Server Group entry, because all other information is retrieved from SQL Server Enterprise Manager registry settings on the local computer. If SQLNSRootType_Server is used, an application must specify a complete connection string because no SQL Server Enterprise Manager registration information is available. The complete initialization and the connection string should look like this:

   Visual Basic

   ' Initialize root object
   eSQLNSRootType = SQLNSRootType_Server
   strConnectString = String(255, 0)
   strConnectString = "Server=MYSERVER;Trusted_Connection=Yes;"
   strAppName = "SQLNS Application; "
   objSQLNS.Initialize strAppName, eSQLNSRootType, Str(strConnectString), hWnd
   If objSQLNS Is Nothing Then
       MsgBox "SQLNamespace could not be initiated. Terminating.",  vbOKOnly, "Error"
   End If
   

   Visual C++

   V_BSTR(&var) = SysAllocString(L"Server=.;Trusted_Connection=Yes;");
   pNS->Initialize(L"SQLNSX Test App", SQLNSRootType_Server, &var, NULL);
   
   'If the ROOTTYPE is SQLNSRootType_Database, add
   'Database=pubs;
   'to the connectstring. Otherwise, the connectstring is the same.
   

4. Traverse the hierachy by establishing a root node in the hierarchy using a call to the ISQLNamespace::GetRootItem method. This root item is of the same type specified in the call to the Initialize method.

   Visual Basic

   Dim hRootItem As Long
   hRootItem = objSQLNS.GetRootItem
   

   All HSQLNSITEM types are stored as Long in Microsoft® Visual Basic®. When a HSQLNSITEM is zero after an assignment, an error occurred. A good practice is to check for a zero HSQLNSITEM handle in your debug build by using asserts like this:

   Debug.Assert hRootItem = 0
   

   To test whether the object type of hRootItem is the same as the root type passed in to call to Initialize, an application can call objSQLNS.GetType(hRootItem) which returns the object type.

   Visual C++

   HSQLNSITEM hServer;
   pNS->GetRootItem(&hServer);
   assert (hServer);
   

5. Walk the hierachy by calling the ISQLNamespace::GetChildItem method until the application reaches the required destination. For example, to invoke the Properties dialog box for dbo in the master database:

   Visual Basic

   Dim hRootItem As Long
   Dim hDatabases As Long
   Dim hDatabase As Long
   Dim hUsers As Long
   Dim hUser As Long
   hRootItem = objSQLNS.GetRootItem
   hDatabases = objSQLNS.GetFirstChildItem(hRootItem, _ SQLNSOBJECTTYPE_DATABASES)
   hDatabase = objSQLNS.GetFirstChildItem(hDatabases, _SQLNSOBJECTTYPE_DATABASE, "master")
   hUsers = objSQLNS.GetFirstChildItem(hDatabase, _SQLNSOBJECTTYPE_DATABASE_USERS)
   hUser = objSQLNS.GetFirstChildItem(hUsers, _SQLNSOBJECTTYPE_DATABASE_USER, "dbo")
   

   Visual C++

   HSQLNSITEM hServer, hDBs, hDB, hUsers, hUser;
   pNS->GetRootItem(&hServer);
   pNS->GetFirstChildItem(hServer, SQLNSOBJECTTYPE_DATABASES, NULL, &hDBs);
   pNS->GetFirstChildItem(hDBs, SQLNSOBJECTTYPE_DATABASE, L"pubs", &hDB);
   pNS->GetFirstChildItem(hDB, SQLNSOBJECTTYPE_DATABASE_USERS, NULL, &hUsers);
   pNS->GetFirstChildItem(hUsers, SQLNSOBJECTTYPE_DATABASE_USER, L"dbo", &hUser);
   

6. Create a SQLNamespaceObject object to allow the user to execute commands that invoke the required user interface components:

   Visual Basic

   Dim objSQLNSObj As SQLNS.SQLNamespaceObject
   Set objSQLNSObj = objSQLNS.GetSQLNamespaceObject(hUser)
   

   Visual C++

   ISQLNamespaceObject* pObjUser = NULL;
   pNS->GetSQLNamespaceObject(hUser, &pObjUser);
   

   You can enumerate all available commands on SQLNamespaceObject objects by iterating through the Commands collection.

7. Execute a command on the SQLNamespaceObject object just created, which invokes the user interface component. An application can execute a command by name or ID. The latter is the prefered method because it is independent of the language of the system. To execute by name, pass the command by calling the ExecuteCommandByName method:

   objSQLNSObj.ExecuteCommandByName "Properties", hWnd,     SQLNamespace_PreferModal
   

   The other two parameters are the handle to the parent window, and the window modality. The modality can be one of three values that indicate the preferred modality:

   • SQLNamespace_DontCare
     
     
   • SQLNamespace_PreferModal
     
     
   • SQLNamespace_PreferModeless

   Note  The SQL Namespace can overrule the requested modality due to default characteristics of the dialog box invoked.

   The preferred way to execute a command by ID is to passing in a constant that indentifies the command:

   objSQLNSObj.ExecuteCommandByID SQLNS_CmdID_PROPERTIES, hWnd, SQLNamespace_PreferModal
   

   An application can also iterate through the Commands collection on the SQLNamespaceObject object. Each item of the collection represents a SQLNamespaceCommand, which has an Execute method.

See Also

SQL-NS Reference