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.
try
{
_SQLServerPtr spSQLServer;
HRESULT hr2 = spSQLServer.CreateInstance(__uuidof(SQLServer));
if (FAILED(hr2))
_com_issue_error(hr2);
if (SUCCEEDED(hr2))
{
try
{
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);
}
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();
}
}
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.