Enumerate SQL Server using SQL DMO

• Download source files - 16 Kb
• Download demo project - 10 Kb

Introduction

SQL Server Distributed Management Objects (SQL-DMO) is a COM-based object library, exposes interfaces that access any object in SQL Server. Any programming language that supports COM objects, such as VB or C++, can use these interfaces.

The SQL-DMO object model allows access to objects in SQL Server so you can build administrative functions into your code. In this article, I'll describe how to Enumerate the Database Servers using SQL-DMO.

SQL DMO Requirements

SQL-DMO uses the Microsoft® SQL Server ODBC driver to connect to and communicate with instances of SQL Server.

If you have installed SQL SERVER Enterprise Manager then you should have the SQL DMO installed. If you don't then the following article shows you how to install the objects.

ADO requirements

MDAC v2.1 or higher (This can be obtained from microsoft) This contains the OLE DB drivers that are required to hook up to the database or the data source.

Using SQL DMO

Use the following import statement to access SQL DMO, this should be done in your stdafx.h file.

#import "c:\mssql7\binn\resources\1033\sqldmo.rll" no_namespace

Using ADO

In Order to use the ADO Com Objects you will need to import the following type library, this should be done in your stdafx.h file. Note that the ADO namespace has been renamed, this needs to be done, otherwise you will find the object name clash between ADO and SQL DMO.

#import "C:\Program Files\Common Files\System\ado\msado15.dll" rename_namespace("ADONS") rename("EOF", "adoEOF") 

Please note  that you may need to add the full path to these dll or you can add the path through your visual C++ environment via Tools->Options->Directories->Include Files

Load COM and OLE Library

One of the first things that you must do is to initialise the COM OLE library in order to use the SQL DMO COM objects, usually this can be done through ::CoInitialize(NULL) and ::CoUninitialize(). If you are using MFC then you can simply call

AfxOleInit()

On the ::InitInstance function of your App class.

Enumerate SQL SERVERS from SQL DMO

The following code shows you the SQL DMO objects that were used to enumerate the SQL SERVER. Basically create the Application object then use the ListAvailableSQLServers method to get a list of the SQL SERVERS.

// set up the Server Combo Box
try
{
    _SQLServerPtr spSQLServer;
     
    HRESULT hr2 = spSQLServer.CreateInstance(__uuidof(SQLServer));
    if (FAILED(hr2))
     _com_issue_error(hr2);   

     if (SUCCEEDED(hr2))
     {
        try
        {
           // Get the Application Ptr
           long lServerCount(0);
           _ApplicationPtr pApplication = spSQLServer->GetApplication();
           if (pApplication) {
                NameListPtr pServerNameList = pApplication->ListAvailableSQLServers();
                if (pServerNameList)
                { 
                    HRESULT  hr= pServerNameList->get_Count(&lServerCount);  BSTRbstrServerName;
                    m_ctlComboBox.ResetContent(); 
                    _variant_t  vIndex(long(0));
                    for (long  i=0;  i <  lServerCount; i++)
                    {
                        vIndex.lVal = i;
                        hr = pServerNameList->raw_Item(vIndex, &bstrServerName);
                        _bstr_t bstrValue(bstrServerName);  
                        CString sValue((LPCSTR)bstrValue);
                        if (!sValue.IsEmpty())
                                m_ctlComboBox.AddString(sValue);
                    }
                       
                    // We know that the server list will not show up on Win 9x machines
                    // therefore check manually to see if SQL Server exist
                    if (m_ctlComboBox.GetCount() == 0)
                    {
                        HKEY hKey = NULL;
                        if (RegOpenKey(HKEY_LOCAL_MACHINE, _T("Software\\Microsoft\\MSSQLServer"), 
                                       &hKey) == ERROR_SUCCESS)
                        {
                            m_ctlComboBox.AddString(_T("(local)"));                                
                            RegCloseKey(hKey);
                        }                            
                    }
                }
            }
            pApplication = NULL;
                
            spSQLServer.Release();
        }
        catch (_com_error e)
        {
            AfxMessageBox(ReportError(e));
            spSQLServer.Release();     //    Free the interface.
        }
    }
    else
    {
        AfxMessageBox("\nUnable to create the SQLServer object.");
    }
 }
 catch (_com_error e)
 {
     AfxMessageBox(ReportError(e));
 }

Note - If you have installed SQL SERVER on a Win 9x machine then the SERVER name is not going to appear in the list. To get round this problem a test in the registry is done to see if SQL SERVER has been installed on the machine, If it has then the (local) server is added to the list.

Conclusion

I have just used a fraction of the avialable functionality that can be exposed from the SQL DMO. The Enumeration of SQL SERVERS example shows how easy it is to use SQL DMO. For more information on SQL DMO see MSDN.