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.*****@oleco. net> skrev i en meddelelse
news:ux******** ******@TK2MSFTN GP14.phx.gbl...
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?
