Click here to Skip to main content
Click here to Skip to main content

How to Exactly Enumerate SQL Server from LAN by using SQLDMO

, 25 Aug 2005
Rate this:
Please Sign up or sign in to vote.
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

Share

About the Author

Wang Xuemin

China China
Hi,hello to everyone. My name is Wang Xuemin and come from China . I like program and i like VC more . My email is 1193525@sina.com,i hope i can receive your letter .
Thank

Comments and Discussions

 
GeneralNot a Good idea for another reason PinmemberAECAEC1-Sep-05 6:47 
GeneralNot a good idea Pinmemberterwin31-Aug-05 6:04 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Mobile
Web03 | 2.8.141015.1 | Last Updated 25 Aug 2005
Article Copyright 2005 by Wang Xuemin
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid