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.
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
HRESULT hr2 = spSQLServer.CreateInstance(__uuidof(SQLServer));
_ApplicationPtr pApplication = spSQLServer->GetApplication();
NameListPtr pServerNameList =
lServerCount = pServerNameList->Count;
for (long i=0; i < lServerCount; i++)
_bstr_t bstrValue(pServerNameList->Item( i ));
pApplication = NULL;
catch (_com_error e)
catch (_com_error e)
SQLDMO::_ApplicationPtr spApplication = NULL;
HRESULT hr = spSQLServer.CreateInstance(__uuidof(SQLDMO::SQLServer));
SQLDMO::NameListPtr spNameList = spApplication->ListAvailableSQLServers();
LONG lCount = spNameList->GetCount();
for ( LONG l = 1; l <= lCount; l++ )
m_ctlComboBox.AddString( (LPCTSTR)spNameList->Item( l ) );
- 2005-08-25 - Article first published.