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?
