List SQL servers in a network

[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[] GetAvailableSQL Servers()
{
// declare arraylist to hold results
ArrayList servers = new ArrayList();

// create and initialize necessary SQL access objects (see SQLDMO.dll)
SQLDMO.Applicat ionClass sqlApp = new SQLDMO.Applicat ionClass();
SQLDMO.NameList sqlServers = sqlApp.ListAvai lableSQLServers ();
for(int i=0;i<sqlServer s.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?

[Benny S. Tordrup]

Re: List SQL servers in a network

Oliver,

Paste the following code into a class module:

<Code>
[DllImport("odbc 32.dll")]
private static extern short SQLAllocHandle( short hType, IntPtr inputHandle,
out IntPtr outputHandle);
[DllImport("odbc 32.dll")]
private static extern short SQLSetEnvAttr(I ntPtr henv, int attribute, IntPtr
valuePtr, int strLength);
[DllImport("odbc 32.dll")]
private static extern short SQLFreeHandle(s hort hType, IntPtr handle);
[DllImport("odbc 32.dll",CharSet =CharSet.Ansi)]
private static extern short SQLBrowseConnec t(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_V ERSION = 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(S QL_DRIVER_STR);
StringBuilder outString = new StringBuilder(D EFAULT_RESULT_S IZE);
short inStringLength = (short) inString.Length ;
short lenNeeded = 0;
try {
if (SQL_SUCCESS == SQLAllocHandle( SQL_HANDLE_ENV, henv, out henv)) {
if (SQL_SUCCESS ==
SQLSetEnvAttr(h env,SQL_ATTR_OD BC_VERSION,(Int Ptr)SQL_OV_ODBC 3,0)) {
if (SQL_SUCCESS == SQLAllocHandle( SQL_HANDLE_DBC, henv, out hconn)) {
if (SQL_NEED_DATA == SQLBrowseConnec t(hconn, inString, inStringLength,
outString, DEFAULT_RESULT_ SIZE, out lenNeeded)) {
if (DEFAULT_RESULT _SIZE < lenNeeded) {
outString.Capac ity = lenNeeded;
if (SQL_NEED_DATA != SQLBrowseConnec t(hconn, inString, inStringLength,
outString, lenNeeded,out lenNeeded)) {
throw new ApplicationExce ption("Unabled to aquire SQL Servers from ODBC
driver.");
}
}
txt = outString.ToStr ing();
int start = txt.IndexOf("{" ) + 1;
int len = txt.IndexOf("}" ) - start;
txt = ((start > 0) && (len > 0)) ? txt = txt.Substring(s tart,len) :
string.Empty;
}
}
}
}
}
catch (Exception ex) {
//Throw away any error if we are not in debug mode
#if (DEBUG)
System.Windows. Forms.MessageBo x.Show(ex.Messa ge,"Fejl ved listning af SQL
Servere");
#endif
txt = string.Empty;
}
finally {
if (hconn != IntPtr.Zero) {
SQLFreeHandle(S QL_HANDLE_DBC,h conn);
}
if (henv != IntPtr.Zero) {
SQLFreeHandle(S QL_HANDLE_ENV,h conn);
}
}
// Get list of local server instances
Microsoft.Win32 .RegistryKey rk =
Microsoft.Win32 .Registry.Local Machine.OpenSub Key(@"Software\ Microsoft\Micro soft
SQL Server");
if (rk != null) {
string[] localServerList = (string[]) rk.GetValue("In stalledInstance s");
foreach (string localServerInst ance in localServerList ) {
switch (localServerIns tance.ToUpper() ) {
case "MSSQLSERVE R":
if (txt.IndexOf("( local)") == -1) txt = "(local)" + (txt.Length > 0 ? "," +
txt : "");
break;
default:
if (txt.IndexOf(Sy stem.Environmen t.MachineName + @"\" + localServerInst ance)
== -1)
txt = (System.Environ ment.MachineNam e + @"\" + localServerInst ance) +
(txt.Length > 0 ? "," + txt : "");
break;
}
}
}
txt = txt.Replace("(l ocal)", System.Environm ent.MachineName );
if (txt.Length > 0) {
retval = txt.Split(",".T oCharArray());
}
return retval;
}
</Code>
"Oliver Braun" <O.Braun@oleco. net> skrev i en meddelelse
news:uxOdH$gqFH A.3352@TK2MSFTN GP14.phx.gbl...[color=blue]
>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[] GetAvailableSQL Servers()
> {
> // declare arraylist to hold results
> ArrayList servers = new ArrayList();
>
> // create and initialize necessary SQL access objects (see SQLDMO.dll)
> SQLDMO.Applicat ionClass sqlApp = new SQLDMO.Applicat ionClass();
> SQLDMO.NameList sqlServers = sqlApp.ListAvai lableSQLServers ();
> for(int i=0;i<sqlServer s.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?
>[/color]

[Jakob Christensen]

RE: List SQL servers in a network

Hi Oliver,
[color=blue]
> - this does not work with Windows XP (see SQLDMO documentation: it works
> only with Windows NT 4.0 and 2000)[/color]

It does work on XP (I have used it). The SQL Server 2000 documentation is
pre-XP.
[color=blue]
> - it does not work on a local PC that is not connected to the network (it
> does not show any instance that is available)[/color]

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?
ListAvailableSQ LServers will only be able to list local instances if the
computer is not connected.

Regards, Jakob.

--

http://www.dotninjas.dk

http://www.powerbytes.dk

[color=blue]
> only with Windows NT 4.0 and 2000)
>
> Does anybody have a better solution for this task?
>
>
>[/color]

[Oliver Braun]

Re: List SQL servers in a network


"Jakob Christensen" <jch@REMOVEpens ion.dk> schrieb im Newsbeitrag
news:EA38F13B-2F00-4AD3-B19C-6C36F24D27E1@mi crosoft.com...
[color=blue]
> ListAvailableSQ LServers will only be able to list local instances if the
> computer is not connected.[/color]

.... 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

[Jakob Christensen]

Re: List SQL servers in a network

It is odd that ListAvailableSQ LServers does not list local instances. It did
work in my case, though.

Regards, Jakob.

--

http://www.dotninjas.dk

http://www.powerbytes.dk

"Oliver Braun" wrote:
[color=blue]
>
> "Jakob Christensen" <jch@REMOVEpens ion.dk> schrieb im Newsbeitrag
> news:EA38F13B-2F00-4AD3-B19C-6C36F24D27E1@mi crosoft.com...
>[color=green]
> > ListAvailableSQ LServers will only be able to list local instances if the
> > computer is not connected.[/color]
>
> .... 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
>
>
>[/color]

[Oliver Braun]

Re: List SQL servers in a network

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" <nospam.bt.nosp am@fk-data.nospam.dk> schrieb im
Newsbeitrag news:%23vOxe8hq FHA.1444@TK2MSF TNGP10.phx.gbl. ..[color=blue]
> Oliver,
>
> Paste the following code into a class module:
>
> <Code>
> [DllImport("odbc 32.dll")]
> private static extern short SQLAllocHandle( short hType, IntPtr
> inputHandle, out IntPtr outputHandle);
> [DllImport("odbc 32.dll")]
> private static extern short SQLSetEnvAttr(I ntPtr henv, int attribute,
> IntPtr valuePtr, int strLength);
> [DllImport("odbc 32.dll")]
> private static extern short SQLFreeHandle(s hort hType, IntPtr handle);
> [DllImport("odbc 32.dll",CharSet =CharSet.Ansi)]
> private static extern short SQLBrowseConnec t(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_V ERSION = 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(S QL_DRIVER_STR);
> StringBuilder outString = new StringBuilder(D EFAULT_RESULT_S IZE);
> short inStringLength = (short) inString.Length ;
> short lenNeeded = 0;
> try {
> if (SQL_SUCCESS == SQLAllocHandle( SQL_HANDLE_ENV, henv, out henv)) {
> if (SQL_SUCCESS ==
> SQLSetEnvAttr(h env,SQL_ATTR_OD BC_VERSION,(Int Ptr)SQL_OV_ODBC 3,0)) {
> if (SQL_SUCCESS == SQLAllocHandle( SQL_HANDLE_DBC, henv, out hconn)) {
> if (SQL_NEED_DATA == SQLBrowseConnec t(hconn, inString, inStringLength,
> outString, DEFAULT_RESULT_ SIZE, out lenNeeded)) {
> if (DEFAULT_RESULT _SIZE < lenNeeded) {
> outString.Capac ity = lenNeeded;
> if (SQL_NEED_DATA != SQLBrowseConnec t(hconn, inString, inStringLength,
> outString, lenNeeded,out lenNeeded)) {
> throw new ApplicationExce ption("Unabled to aquire SQL Servers from ODBC
> driver.");
> }
> }
> txt = outString.ToStr ing();
> int start = txt.IndexOf("{" ) + 1;
> int len = txt.IndexOf("}" ) - start;
> txt = ((start > 0) && (len > 0)) ? txt = txt.Substring(s tart,len) :
> string.Empty;
> }
> }
> }
> }
> }
> catch (Exception ex) {
> //Throw away any error if we are not in debug mode
> #if (DEBUG)
> System.Windows. Forms.MessageBo x.Show(ex.Messa ge,"Fejl ved listning af SQL
> Servere");
> #endif
> txt = string.Empty;
> }
> finally {
> if (hconn != IntPtr.Zero) {
> SQLFreeHandle(S QL_HANDLE_DBC,h conn);
> }
> if (henv != IntPtr.Zero) {
> SQLFreeHandle(S QL_HANDLE_ENV,h conn);
> }
> }
> // Get list of local server instances
> Microsoft.Win32 .RegistryKey rk =
> Microsoft.Win32 .Registry.Local Machine.OpenSub Key(@"Software\ Microsoft\Micro soft
> SQL Server");
> if (rk != null) {
> string[] localServerList = (string[]) rk.GetValue("In stalledInstance s");
> foreach (string localServerInst ance in localServerList ) {
> switch (localServerIns tance.ToUpper() ) {
> case "MSSQLSERVE R":
> if (txt.IndexOf("( local)") == -1) txt = "(local)" + (txt.Length > 0 ? ","
> + txt : "");
> break;
> default:
> if (txt.IndexOf(Sy stem.Environmen t.MachineName + @"\" +
> localServerInst ance) == -1)
> txt = (System.Environ ment.MachineNam e + @"\" + localServerInst ance) +
> (txt.Length > 0 ? "," + txt : "");
> break;
> }
> }
> }
> txt = txt.Replace("(l ocal)", System.Environm ent.MachineName );
> if (txt.Length > 0) {
> retval = txt.Split(",".T oCharArray());
> }
> return retval;
> }
> </Code>
> "Oliver Braun" <O.Braun@oleco. net> skrev i en meddelelse
> news:uxOdH$gqFH A.3352@TK2MSFTN GP14.phx.gbl...[color=green]
>>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[] GetAvailableSQL Servers()
>> {
>> // declare arraylist to hold results
>> ArrayList servers = new ArrayList();
>>
>> // create and initialize necessary SQL access objects (see SQLDMO.dll)
>> SQLDMO.Applicat ionClass sqlApp = new SQLDMO.Applicat ionClass();
>> SQLDMO.NameList sqlServers = sqlApp.ListAvai lableSQLServers ();
>> for(int i=0;i<sqlServer s.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?
>>[/color]
>
>[/color]