List SQL Servers on a Network

How I can list SQL servers on a network at C#?

I once needed the exact same functionality and found the following code somewhere on the internet:

#include < sql.h >
#include < sqlext.h >

BOOL EnumerateServers()
{
   LPCTSTR pszInputParam = _T("Driver={SQL Server}");
   LPCTSTR pszLookUpKey = _T("SERVER:Server=");

   SQLHENV  hSQLEnv;
   SQLHDBC  hSQLHdbc;
   short    sConnStrOut;
   DWORD    dwRetCode;

   //Allocate the environment handle
   dwRetCode = SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &hSQLEnv);

   if (dwRetCode == SQL_SUCCESS || dwRetCode == SQL_SUCCESS_WITH_INFO)
   {
      //Set the environment attribute to SQL_OV_ODBC3
      dwRetCode = SQLSetEnvAttr(hSQLEnv, SQL_ATTR_ODBC_VERSION, (void *)SQL_OV_ODBC3, 0);

      if (dwRetCode == SQL_SUCCESS || dwRetCode == SQL_SUCCESS_WITH_INFO) 
      {
         //Allocate a connection handle
         dwRetCode = SQLAllocHandle(SQL_HANDLE_DBC, hSQLEnv, &hSQLHdbc);

         if (dwRetCode == SQL_SUCCESS || dwRetCode == SQL_SUCCESS_WITH_INFO) 
         {
            CString szConnStrOut;

            //Call SQLBrowseConnect for additional information
            dwRetCode = SQLBrowseConnect(hSQLHdbc, 
                                         (SQLCHAR *)pszInputParam, 
                                         SQL_NTS,
                                         (SQLCHAR *)(szConnStrOut.GetBuffer(ENUM_SERVERS_MAX_RET_LENGTH)),
                                         ENUM_SERVERS_MAX_RET_LENGTH,
                                         &sConnStrOut);
            szConnStrOut.ReleaseBuffer();

            //if the look up key is found
            //fill in the result set
            int iFind = szConnStrOut.Find(pszLookUpKey);
            if(iFind != -1)
            {
               CString szLookUpKey = pszLookUpKey;
               szConnStrOut = szConnStrOut.Mid(iFind+szLookUpKey.GetLength());

               iFind = szConnStrOut.Find('{');
               if(iFind != -1)
               {
                  szConnStrOut = szConnStrOut.Mid(iFind+1);
                  iFind = szConnStrOut.Find('}');
                  if(iFind != -1)
                  {
                     szConnStrOut = szConnStrOut.Left(iFind);

                     // szConnStrOut contains all servers seperated by a comma 
                     // do with it what you like

                     dwRetCode = TRUE;
                  }
               }
            }	

            SQLDisconnect(hSQLHdbc);
         }
         SQLFreeHandle(SQL_HANDLE_DBC, hSQLHdbc);
      }
      SQLFreeHandle(SQL_HANDLE_ENV, hSQLEnv);
   }
   return dwRetCode;
}

I can't remember where I found it, but it works fine for me. Smile | :)

Calling the NetServerEnum function seems cleaner but the only problem is it won't function on Windows 9x/ME (according to the MSDN). My function will. Smile | :)

Rogier.

I'm might be wrong about this, but I thought SQLBrowseConnect only enumerates ODBC sources?

Not much use if using OLE-DB (much faster and more ADO compatability)

Theres a feature for this in the SQL-DMO libs. Technically they're server management, but it does the job.

http://support.microsoft.com/default.aspx?scid=kb;EN-US;q287737

from that page: (microsofts fault if duff..)

Private Sub Command1_Click()
Dim i As Integer
Dim oNames As SQLDMO.NameList
Dim oSQLApp As SQLDMO.Application
Set oSQLApp = New SQLDMO.Application

Set oNames = oSQLApp.ListAvailableSQLServers()
List1.Clear
For i = 1 To oNames.Count
List1.AddItem oNames.Item(i)
Next i
End Sub

-Ed

Hi,

You're absolutely right about SQLBrowseConnect, it only enumerates ODBC sources. (according to the MSDN)

But, correct me if I'm wrong, doesn't every OLE-DB source
have an ODBC equivalent? In other words, doesn't every
SQL-Server accept both ODBC and OLE-DB connections?
(or can one disable ODBC-connections in SQL-server?)

I only use SQLBrowseConnect to enumerate the servers,
afterwards I connect using (the faster) OLE-DB (with ADO).

Rogier.

A small problem with the code posted in the original article is that it does not display non-default instances of SQL servers. For example, we have two instances named "ACCOUNTING" and "ACCOUNTING\TEST" and the example code only shows "ACCOUNTING", while the list of servers in Query Analyzer lists both.

But this code is not working in VB.net though I have referenced this library.
Can U help me out ?

Hi,

Well all I had to do to get it working in vb.net was this:

1. add reference to the dll
2. add some controls to a form
- a textbox called txtDomain
- a listbox called lstSvrs
- a button called btnSearch
3. add the following code for the button's click handler

<br />
Private Sub btnSearch_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSearch.Click<br />
    Dim oSqlUtils As New SQLUtils.ListServers()<br />
    Dim svr As String<br />
<br />
    oSqlUtils.Domain = txtDomain.Text<br />
    oSqlUtils.GetSQLServers()<br />
<br />
    For Each svr In oSqlUtils.Servers<br />
        lstSvrs.Items.Add(svr)<br />
    Next<br />
End Sub<br />

When you say "it's not working" what exactly do you mean? Are you getting an error message? Let me know if you're still having trouble and I'll try and help you out.

HTH

mostevil wrote:
I'm might be wrong about this, but I thought SQLBrowseConnect only enumerates ODBC sources?

In MSDN it says that the SQL-DMO function ListAvailableSQLServers function uses SQLBrowseConnect to enumerate the servers anyway.

Was there ever a complete solution to this problem. I'm trying to find out how to get a list of SQL servers which also:

Works on Win9x/ME as well as NT/2K & XP
Works when not connected to a network
Picks up all instances of SQL and MSDE

So far, the SQLDMO won't pick up when not on a network and the NetServerEnum won't work on win 9x/Me.

Any help would be much appreciated.

p.s. If anyone who is willing to lend me their Dodge Viper for a few days, please let me know.

Here goes the same in VB.Net (I've ported C# article on http://www.ondotnet.pl/sourcecode/SqlLocator.cs)

Imports System
Imports System.Text
Imports System.Runtime.InteropServices

Public Class SqlServerList

#Region "ODBC32 API"
Private Const SQL_HANDLE_ENV As Short = 1
Private Const SQL_HANDLE_DBC As Short = 2
Private Const SQL_ATTR_ODBC_VERSION As Integer = 200
Private Const SQL_OV_ODBC3 As Integer = 3
Private Const SQL_SUCCESS As Short = 0
Private Const SQL_NEED_DATA As Short = 99
Private Const DEFAULT_RESULT_SIZE As Short = 1024
Private Const SQL_DRIVER_STR As String = "DRIVER=SQL SERVER"

<DllImport("odbc32.dll")> _
Private Shared Function SQLAllocHandle(ByVal hType As Short, ByVal inputHandle As IntPtr, ByRef outputHandle As IntPtr) As Short
End Function
<DllImport("odbc32.dll")> _
Private Shared Function SQLSetEnvAttr(ByVal henv As IntPtr, ByVal attribute As Integer, ByVal valuePtr As IntPtr, ByVal strLength As Integer) As Short
End Function
<DllImport("odbc32.dll", CharSet:=CharSet.Ansi)> _
Private Shared Function SQLBrowseConnect(ByVal hconn As IntPtr, ByVal inString As StringBuilder, ByVal inStringLength As Short, ByVal outString As StringBuilder, ByVal outStringLength As Short, ByRef outLengthNeeded As Short) As Short
End Function
<DllImport("odbc32.dll")> _
Private Shared Function SQLFreeHandle(ByVal hType As Short, ByVal handle As IntPtr) As Short
End Function
#End Region

'Lists SQL Server databases
'usage: comboBox1.Items.AddRange(SqlServerList.Servers())
Public Shared Function Servers() As String()

Dim retval As String() = Nothing
Dim txt As String = String.Empty
Dim henv As IntPtr = IntPtr.Zero
Dim hconn As IntPtr = IntPtr.Zero
Dim inString As New StringBuilder(SQL_DRIVER_STR)
Dim outString As New StringBuilder(DEFAULT_RESULT_SIZE)
Dim inStringLength As Short = Fix(CShort(inString.Length))
Dim lenNeeded As Short = 0

Try
If SQL_SUCCESS = SQLAllocHandle(SQL_HANDLE_ENV, henv, henv) Then
If SQL_SUCCESS = SQLSetEnvAttr(henv, SQL_ATTR_ODBC_VERSION, New System.IntPtr(SQL_OV_ODBC3), 0) Then
If SQL_SUCCESS = SQLAllocHandle(SQL_HANDLE_DBC, henv, hconn) Then
If SQL_NEED_DATA = SQLBrowseConnect(hconn, inString, inStringLength, outString, DEFAULT_RESULT_SIZE, lenNeeded) Then
If DEFAULT_RESULT_SIZE < lenNeeded Then
outString.Capacity = lenNeeded
If SQL_NEED_DATA <> SQLBrowseConnect(hconn, inString, inStringLength, outString, lenNeeded, lenNeeded) Then
Throw New ApplicationException("Unabled to aquire SQL Servers from ODBC driver.")
End If
End If
txt = outString.ToString()
Dim start As Integer = txt.IndexOf("{") + 1
Dim len As Integer = txt.IndexOf("}") - start
If start > 0 AndAlso len > 0 Then
txt = txt.Substring(start, len)
Else
txt = String.Empty
End If
End If
End If
End If
End If
Catch ex As Exception
txt = String.Empty
Throw ex
Finally
If Not hconn.Equals(IntPtr.Zero) Then
SQLFreeHandle(SQL_HANDLE_DBC, hconn)
End If
If Not henv.Equals(IntPtr.Zero) Then
SQLFreeHandle(SQL_HANDLE_ENV, hconn)
End If
End Try

If txt.Length > 0 Then
retval = txt.Split(",".ToCharArray())
End If

Return retval
End Function

End Class

You just saved me a ton of time. I normally use C# and have a customer who wanted a program in VB (so his programmer can maintain it). I was looking at converting SqlLocator and found this. Big Grin | :-D

Thanks!

This code works like a dream. Thanks bud.

zjerry wrote:
Here goes the same in VB.Net (I've ported C# article on http://www.ondotnet.pl/sourcecode/SqlLocator.cs)

Imports System
Imports System.Text
Imports System.Runtime.InteropServices

Public Class SqlServerList ...........

Hello!

I tried this code without connecting my PC to a network and it returned nothing even if there is a running instance of SQL-Server(local).

Is this correct? Is was one of the requests that it even lists non-default instances and that it works even without network connection....

Any idea?

Thanks!
Frank

Using the code ZJERRY has posted....

I had been looking all over for something like this and I am very glad I found this. Unfortunately the system I am working on has upwards of 500 servers available and to list them all is taking upwards of ten minutes. Cry | :((

Has anyone else had this problem, or does anyone have any suggestions on how to speed it up?

Here is my current code:

Dim i As Integer
Dim ServerList As SqlServerList
Dim ServerNumber As Integer = ServerList.Servers.Length

For i = 0 To ServerNumber - 1
combo_db.Items.Add(ServerList.Servers(i))
Next i

Any Help would be greatly appreciated.

-- modified at 16:11 Wednesday 15th February, 2006

rugbygeek wrote:
For i = 0 To ServerNumber - 1
combo_db.Items.Add(ServerList.Servers(i))
Next i

combo_db.Items.AddRange(ServerList.Servers())
may be a little faster

Pablo

It's a nice method to get the list of servers...
How should i get the list of databases available in the particular server without using SQLDMO.dll

Loka

Thank you...This is very helpfull! You got my 5 Wink | ;)

Pablo

Will it be work for Oracle, if I cange Driver details("DRIVER=SQL SERVER")?
I tried it, not working. I want get the list of oracle servers connected to my machine.

Loka

List SQL Servers on a Network

Introduction

History

License

Comments and Discussions