List SQL servers in a network

  • Thread starter Thread starter Oliver Braun
  • Start date Start date
O

Oliver Braun

I know this is a very common issue and I found a lot of hints on this topic
in www but I did not find a very good solution for this task.

Most of the solutions use SQLDMO to list all sql servers in the network like
this C# code:

public static string[] GetAvailableSQLServers()
{
// declare arraylist to hold results
ArrayList servers = new ArrayList();

// create and initialize necessary SQL access objects (see SQLDMO.dll)
SQLDMO.ApplicationClass sqlApp = new SQLDMO.ApplicationClass();
SQLDMO.NameList sqlServers = sqlApp.ListAvailableSQLServers();
for(int i=0;i<sqlServers.Count;i++)
{
object srv = sqlServers.Item(i + 1);
if(srv != null)
{
servers.Add(srv.ToString());
}
}
// convert arraylist to string array and return it
return servers.ToArray(Type.GetType("System.String")) as string[];
}

But there are two main problems:
- this does not work with Windows XP (see SQLDMO documentation: it works
only with Windows NT 4.0 and 2000)
- it does not work on a local PC that is not connected to the network (it
does not show any instance that is available)

Does anybody have a better solution for this task?
 
Oliver,

Paste the following code into a class module:

<Code>
[DllImport("odbc32.dll")]
private static extern short SQLAllocHandle(short hType, IntPtr inputHandle,
out IntPtr outputHandle);
[DllImport("odbc32.dll")]
private static extern short SQLSetEnvAttr(IntPtr henv, int attribute, IntPtr
valuePtr, int strLength);
[DllImport("odbc32.dll")]
private static extern short SQLFreeHandle(short hType, IntPtr handle);
[DllImport("odbc32.dll",CharSet=CharSet.Ansi)]
private static extern short SQLBrowseConnect(IntPtr hconn, StringBuilder
inString,
short inStringLength, StringBuilder outString, short outStringLength,
out short outLengthNeeded);
private const short SQL_HANDLE_ENV = 1;
private const short SQL_HANDLE_DBC = 2;
private const int SQL_ATTR_ODBC_VERSION = 200;
private const int SQL_OV_ODBC3 = 3;
private const short SQL_SUCCESS = 0;
private const short SQL_NEED_DATA = 99;
private const short DEFAULT_RESULT_SIZE = 1024;
private const string SQL_DRIVER_STR = "DRIVER=SQL SERVER";
public static string[] GetServers() {
string[] retval = null;
string txt = string.Empty;
IntPtr henv = IntPtr.Zero;
IntPtr hconn = IntPtr.Zero;
StringBuilder inString = new StringBuilder(SQL_DRIVER_STR);
StringBuilder outString = new StringBuilder(DEFAULT_RESULT_SIZE);
short inStringLength = (short) inString.Length;
short lenNeeded = 0;
try {
if (SQL_SUCCESS == SQLAllocHandle(SQL_HANDLE_ENV, henv, out henv)) {
if (SQL_SUCCESS ==
SQLSetEnvAttr(henv,SQL_ATTR_ODBC_VERSION,(IntPtr)SQL_OV_ODBC3,0)) {
if (SQL_SUCCESS == SQLAllocHandle(SQL_HANDLE_DBC, henv, out hconn)) {
if (SQL_NEED_DATA == SQLBrowseConnect(hconn, inString, inStringLength,
outString, DEFAULT_RESULT_SIZE, out lenNeeded)) {
if (DEFAULT_RESULT_SIZE < lenNeeded) {
outString.Capacity = lenNeeded;
if (SQL_NEED_DATA != SQLBrowseConnect(hconn, inString, inStringLength,
outString, lenNeeded,out lenNeeded)) {
throw new ApplicationException("Unabled to aquire SQL Servers from ODBC
driver.");
}
}
txt = outString.ToString();
int start = txt.IndexOf("{") + 1;
int len = txt.IndexOf("}") - start;
txt = ((start > 0) && (len > 0)) ? txt = txt.Substring(start,len) :
string.Empty;
}
}
}
}
}
catch (Exception ex) {
//Throw away any error if we are not in debug mode
#if (DEBUG)
System.Windows.Forms.MessageBox.Show(ex.Message,"Fejl ved listning af SQL
Servere");
#endif
txt = string.Empty;
}
finally {
if (hconn != IntPtr.Zero) {
SQLFreeHandle(SQL_HANDLE_DBC,hconn);
}
if (henv != IntPtr.Zero) {
SQLFreeHandle(SQL_HANDLE_ENV,hconn);
}
}
// Get list of local server instances
Microsoft.Win32.RegistryKey rk =
Microsoft.Win32.Registry.LocalMachine.OpenSubKey(@"Software\Microsoft\Microsoft
SQL Server");
if (rk != null) {
string[] localServerList = (string[]) rk.GetValue("InstalledInstances");
foreach (string localServerInstance in localServerList) {
switch (localServerInstance.ToUpper()) {
case "MSSQLSERVER":
if (txt.IndexOf("(local)") == -1) txt = "(local)" + (txt.Length > 0 ? "," +
txt : "");
break;
default:
if (txt.IndexOf(System.Environment.MachineName + @"\" + localServerInstance)
== -1)
txt = (System.Environment.MachineName + @"\" + localServerInstance) +
(txt.Length > 0 ? "," + txt : "");
break;
}
}
}
txt = txt.Replace("(local)", System.Environment.MachineName);
if (txt.Length > 0) {
retval = txt.Split(",".ToCharArray());
}
return retval;
}
</Code>
Oliver Braun said:
I know this is a very common issue and I found a lot of hints on this topic
in www but I did not find a very good solution for this task.

Most of the solutions use SQLDMO to list all sql servers in the network
like this C# code:

public static string[] GetAvailableSQLServers()
{
// declare arraylist to hold results
ArrayList servers = new ArrayList();

// create and initialize necessary SQL access objects (see SQLDMO.dll)
SQLDMO.ApplicationClass sqlApp = new SQLDMO.ApplicationClass();
SQLDMO.NameList sqlServers = sqlApp.ListAvailableSQLServers();
for(int i=0;i<sqlServers.Count;i++)
{
object srv = sqlServers.Item(i + 1);
if(srv != null)
{
servers.Add(srv.ToString());
}
}
// convert arraylist to string array and return it
return servers.ToArray(Type.GetType("System.String")) as string[];
}

But there are two main problems:
- this does not work with Windows XP (see SQLDMO documentation: it works
only with Windows NT 4.0 and 2000)
- it does not work on a local PC that is not connected to the network (it
does not show any instance that is available)

Does anybody have a better solution for this task?
Click to expand...
 
Hi Oliver,
- this does not work with Windows XP (see SQLDMO documentation: it works
only with Windows NT 4.0 and 2000)
Click to expand...

It does work on XP (I have used it). The SQL Server 2000 documentation is
pre-XP.
- it does not work on a local PC that is not connected to the network (it
does not show any instance that is available)
Click to expand...

I do not understand this question. How do you expect to be able to list SQL
Servers on the network when the standalone is not connected?
ListAvailableSQLServers will only be able to list local instances if the
computer is not connected.

Regards, Jakob.
 
ListAvailableSQLServers will only be able to list local instances if the
computer is not connected.
Click to expand...

.... of course, I do not expect to get response from outside if I am not
connected but with my code I even get no response of the local instances !!!
Don't know why...

I will try the code of Benny (thanks for it). As far as I can see he looks
for local instances by accessing the local registry additionally to the
network scan.

Best regards
Oliver
 
It is odd that ListAvailableSQLServers does not list local instances. It did
work in my case, though.

Regards, Jakob.
 
Hallo Benny,

your code really works very well.

Let me just tell you (and the community) an experience that I made: I tried
your code on my PC connected to a network with several SQL-servers running
on different places, even multi-instances on a workstation. Some of them did
not appear in the list of the returned servers and it took a while to find
out that this was because of the windows firewall.
Just as an information...

Best regards and many thanks to dk
Oliver


Benny S. Tordrup said:
Oliver,

Paste the following code into a class module:

<Code>
[DllImport("odbc32.dll")]
private static extern short SQLAllocHandle(short hType, IntPtr
inputHandle, out IntPtr outputHandle);
[DllImport("odbc32.dll")]
private static extern short SQLSetEnvAttr(IntPtr henv, int attribute,
IntPtr valuePtr, int strLength);
[DllImport("odbc32.dll")]
private static extern short SQLFreeHandle(short hType, IntPtr handle);
[DllImport("odbc32.dll",CharSet=CharSet.Ansi)]
private static extern short SQLBrowseConnect(IntPtr hconn, StringBuilder
inString,
short inStringLength, StringBuilder outString, short outStringLength,
out short outLengthNeeded);
private const short SQL_HANDLE_ENV = 1;
private const short SQL_HANDLE_DBC = 2;
private const int SQL_ATTR_ODBC_VERSION = 200;
private const int SQL_OV_ODBC3 = 3;
private const short SQL_SUCCESS = 0;
private const short SQL_NEED_DATA = 99;
private const short DEFAULT_RESULT_SIZE = 1024;
private const string SQL_DRIVER_STR = "DRIVER=SQL SERVER";
public static string[] GetServers() {
string[] retval = null;
string txt = string.Empty;
IntPtr henv = IntPtr.Zero;
IntPtr hconn = IntPtr.Zero;
StringBuilder inString = new StringBuilder(SQL_DRIVER_STR);
StringBuilder outString = new StringBuilder(DEFAULT_RESULT_SIZE);
short inStringLength = (short) inString.Length;
short lenNeeded = 0;
try {
if (SQL_SUCCESS == SQLAllocHandle(SQL_HANDLE_ENV, henv, out henv)) {
if (SQL_SUCCESS ==
SQLSetEnvAttr(henv,SQL_ATTR_ODBC_VERSION,(IntPtr)SQL_OV_ODBC3,0)) {
if (SQL_SUCCESS == SQLAllocHandle(SQL_HANDLE_DBC, henv, out hconn)) {
if (SQL_NEED_DATA == SQLBrowseConnect(hconn, inString, inStringLength,
outString, DEFAULT_RESULT_SIZE, out lenNeeded)) {
if (DEFAULT_RESULT_SIZE < lenNeeded) {
outString.Capacity = lenNeeded;
if (SQL_NEED_DATA != SQLBrowseConnect(hconn, inString, inStringLength,
outString, lenNeeded,out lenNeeded)) {
throw new ApplicationException("Unabled to aquire SQL Servers from ODBC
driver.");
}
}
txt = outString.ToString();
int start = txt.IndexOf("{") + 1;
int len = txt.IndexOf("}") - start;
txt = ((start > 0) && (len > 0)) ? txt = txt.Substring(start,len) :
string.Empty;
}
}
}
}
}
catch (Exception ex) {
//Throw away any error if we are not in debug mode
#if (DEBUG)
System.Windows.Forms.MessageBox.Show(ex.Message,"Fejl ved listning af SQL
Servere");
#endif
txt = string.Empty;
}
finally {
if (hconn != IntPtr.Zero) {
SQLFreeHandle(SQL_HANDLE_DBC,hconn);
}
if (henv != IntPtr.Zero) {
SQLFreeHandle(SQL_HANDLE_ENV,hconn);
}
}
// Get list of local server instances
Microsoft.Win32.RegistryKey rk =
Microsoft.Win32.Registry.LocalMachine.OpenSubKey(@"Software\Microsoft\Microsoft
SQL Server");
if (rk != null) {
string[] localServerList = (string[]) rk.GetValue("InstalledInstances");
foreach (string localServerInstance in localServerList) {
switch (localServerInstance.ToUpper()) {
case "MSSQLSERVER":
if (txt.IndexOf("(local)") == -1) txt = "(local)" + (txt.Length > 0 ? ","
+ txt : "");
break;
default:
if (txt.IndexOf(System.Environment.MachineName + @"\" +
localServerInstance) == -1)
txt = (System.Environment.MachineName + @"\" + localServerInstance) +
(txt.Length > 0 ? "," + txt : "");
break;
}
}
}
txt = txt.Replace("(local)", System.Environment.MachineName);
if (txt.Length > 0) {
retval = txt.Split(",".ToCharArray());
}
return retval;
}
</Code>
Oliver Braun said:
I know this is a very common issue and I found a lot of hints on this
topic in www but I did not find a very good solution for this task.

Most of the solutions use SQLDMO to list all sql servers in the network
like this C# code:

public static string[] GetAvailableSQLServers()
{
// declare arraylist to hold results
ArrayList servers = new ArrayList();

// create and initialize necessary SQL access objects (see SQLDMO.dll)
SQLDMO.ApplicationClass sqlApp = new SQLDMO.ApplicationClass();
SQLDMO.NameList sqlServers = sqlApp.ListAvailableSQLServers();
for(int i=0;i<sqlServers.Count;i++)
{
object srv = sqlServers.Item(i + 1);
if(srv != null)
{
servers.Add(srv.ToString());
}
}
// convert arraylist to string array and return it
return servers.ToArray(Type.GetType("System.String")) as string[];
}

But there are two main problems:
- this does not work with Windows XP (see SQLDMO documentation: it works
only with Windows NT 4.0 and 2000)
- it does not work on a local PC that is not connected to the network (it
does not show any instance that is available)

Does anybody have a better solution for this task?
Click to expand...
Click to expand...
 
Back
Top