How to Exactly Enumerate SQL Server from LAN by using SQLDMO

• Download demo project - 35.9 Kb

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.

1. #import "C:\Program Files\Microsoft SQL Server\80\Tools\Binn\Resources\1033\sqldmo.rll" no_namespace
2. #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:

1. Rashid Thadha: Enumerate SQL Server using SQL DMO.

   The same as mine, could not enumerate all.

2. Santosh Rao: SQL Server and Database Enumerator.

   The program could enumerate correctly but it uses ODBC not SQLDMO.

3. 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

 // if you use method 1,then you shold write as
 try
 {
   _SQLServerPtr spSQLServer;

   HRESULT hr2 = spSQLServer.CreateInstance(__uuidof(SQLServer));
   if (SUCCEEDED(hr2))
   {
      try
      {
         // Get the Application Ptr
         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");
}

// if you use method 2,the you should write as
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.