Click here to Skip to main content
65,938 articles
CodeProject is changing. Read more.
Articles / database / SQL-Server

How to Exactly Enumerate SQL Server from LAN by using SQLDMO

4.00/5 (2 votes)
25 Aug 20052 min read 1   776  
An article on how to use SQLDMO to enumerate SQL Server instances.

 

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.

License

This article has no explicit license attached to it but may contain usage terms in the article text or the download files themselves. If in doubt please contact the author via the discussion board below.

A list of licenses authors might use can be found here