Click here to Skip to main content
15,867,686 members
Articles / Desktop Programming / MFC
Article

Enumerate SQL Server using SQL DMO

Rate me:
Please Sign up or sign in to vote.
4.33/5 (3 votes)
4 Dec 20012 min read 92K   3.5K   36   4
Get a list of SQL Servers to which you can connect.

Sample Image - serverenum.gif

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.

License

This article has no explicit license attached to it but may contain usage terms in the article text or the download files themselves. If in doubt please contact the author via the discussion board below.

A list of licenses authors might use can be found here


Written By
Web Developer
United Kingdom United Kingdom
Was made redundant in early 2003 after 10 years in computer programming, since then started my own business (selling computer books on the net)
www.pricecutbook.co.uk


Comments and Discussions

 
GeneralSQLServer object is not required Pin
sotnikov28-Jun-04 1:19
sotnikov28-Jun-04 1:19 
_ApplicationPtr spApp;
if (SUCCEEDED(spApp.CreateInstance(__uuidof(Application))))
{
// Get the list of servers.
NameListPtr spNameList = spApp->ListAvailableSQLServers();
if (spNameList)
{
long lCount = spNameList->GetCount();
_bstr_t bstrName;

for(long i = 0; i < lCount; i++)
{
bstrName = spNameList->Item(i);
if (bstrName.length()>0) names.push_back((LPCTSTR)bstrName);
}
}

spApp.Release();
}
QuestionHow do I run this on a new PC Pin
Tom Wright5-Apr-04 11:29
Tom Wright5-Apr-04 11:29 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.