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 that is required after the object is declared, and 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 a Execute method.

See Also

SQL-NS Reference