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 your program, you may find that some SQL Server databases 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"
Using method 1 can get the result shown in the right image. Using method 2 can get the result shown in the 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 instances from a 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 in which SQL Server was installed on could not be listed. It was strange. Then I wrote it in VB, and it could enumerate all SQL Server instances. I wrote another program by using ODBC, it could enumerate every SQL Server. I was more and more surprised. I decided to look for help from Internet. I saerched for sample files from CodeProject to test. The results list was as follows:
- Rashid Thadha: Enumerate SQL Server using SQL DMO.
The same as mine, could not enumerate all.
- Santosh Rao: SQL Server and Database Enumerator.
The program could enumerate correctly but it uses ODBC not SQLDMO.
- Armen Hakobyan: Shrinking SQL Server Transaction Logs with SQL-DMO.
The .exe file could enumerate correctly, but the source code could not be compiled in VC 6.
And so on...
I analyzed my code very carefully, I found the function ListAvailableSQLServers()
could always get the right amount every time. But spNameList->Item(i)
could not return the right result every time. So I even guessed 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 rewrote my program with method 2, and the new program could enumerate all SQL Server instances. I was so happy that I wrote this article at once to let other people share my pleasure.
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 ) );
History
- 2005-08-25 - Article first published.