How To Exactly Enumerate SQL Server From LAN By Using SQLDMO

• Download Demo Project - 74.3 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 you program,you maybe find that some SQL Server 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"

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

1. Rashid Thadha ( http://www.codeproject.com/database/serverenum.asp)
   The same as my,can not enumerate all
2. Santosh Rao (http://www.codeproject.com/database/sqlsrvenumerator.asp)
   The program can enumerate correctly but it use ODBC not SQLDMO
3. Armen Hakobyan (http://www.codeproject.com/database/ShrinkingSQLServerTransLo.asp)
   The .exe file can enumerate correctly ,but the source code can not by compiled in vc 6,And so on

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

// 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 ) ); //