Introduction
Many people know that SQLDMO can be used to enumerate SQL Server,but perhaps little people know there is a problem when using SQLDMO . If you don’t use SQLDMO correctly in you program,you maybe find that some SQL Server can not be enumerated.
#import "C:\Program Files\Microsoft SQL Server\80\Tools\Binn\Resources\1033\sqldmo.rll" no_namespace
#import "C:\Program Files\Microsoft SQL Server\80\Tools\Binn\Resources\1033\sqldmo.rll"
Use method 1 can get the right result( right image), use method 2 can get left right result(left image). You may notice that the left image lacks one SQL Server machine
Background
I wrote a VC program to enumerate all SQL Server from LAN two weeks ago, like most people i wrote the program with the method 1. The program seemed to work rightly .but I found that one machine which installed SQL Server on could not be listed. I was strange. Then I wrote it in VB,it could Enumerate all SQL Server,I wrote another program by using ODBC, it could enumerate ervery SQL Server. I was more strange. I decided to look for help from Internet . I downloaded many sample files from www.codeproject.com to test . The results list as follows
I analyzed my code very carefully,I found the function ListAvailableSQLServers() can always get right amount every time.but spNameList->Item(i) can not return right result every time ,so I even guess that the data type _bstr_t has a bug. I wasted many days on _bstr_t. I certainly had no gain and I was so disappointed.
By chance I rewote my program with method 2,wonder appeared,the new program could enumerate all SQL Server .I was so happy that I wrote this article at once to let other people to share my pleasue.
Using the code
try
{
_SQLServerPtr spSQLServer;
HRESULT hr2 = spSQLServer.CreateInstance(__uuidof(SQLServer));
if (SUCCEEDED(hr2))
{
try
{
long lServerCount;
_ApplicationPtr pApplication = spSQLServer->GetApplication();
if (pApplication)
{
NameListPtr pServerNameList = pApplication->ListAvailableSQLServers();
if (pServerNameList)
{
lServerCount = pServerNameList->Count;
for (long i=0; i < lServerCount; i++)
{
_bstr_t bstrValue(pServerNameList->Item( i ));
CString sName((LPCSTR)bstrValue);
if (!sName.IsEmpty())
m_ctlComboBox.AddString(sName);
}
}
}
pApplication = NULL;
spSQLServer.Release();
}
catch (_com_error e)
{
spSQLServer.Release();
}
}
else
{
AfxMessageBox("Create Error.");
}
}
catch (_com_error e)
{
AfxMessageBox("Error");
}
SQLDMO::_ApplicationPtr spApplication = NULL;
SQLDMO::_SQLServerPtr spSQLServer;
HRESULT hr = spSQLServer.CreateInstance(__uuidof(SQLDMO::SQLServer));
if (FAILED(hr))
{
AfxMessageBox("CreateInstance() error!");
return;
}
spApplication =spSQLServer->GetApplication();
SQLDMO::NameListPtr spNameList = spApplication->ListAvailableSQLServers();
LONG lCount = spNameList->GetCount();
for ( LONG l = 1; l <= lCount; l++ )
m_ctlComboBox.AddString( (LPCTSTR)spNameList->Item( l ) );