How To Exactly Enumerate SQL Server From LAN By Using SQLDMO





3.00/5 (4 votes)
An article on introducing how to use SQLDMO
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.
- #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"
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
- Rashid Thadha ( http://www.codeproject.com/database/serverenum.asp)
The same as my,can not enumerate all - Santosh Rao (http://www.codeproject.com/database/sqlsrvenumerator.asp)
The program can enumerate correctly but it use ODBC not SQLDMO - 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 ) ); //