Re: List SQL servers in a network
- From: "Oliver Braun" <O.Braun@xxxxxxxxx>
- Date: Fri, 26 Aug 2005 12:33:25 +0200
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.nospam@xxxxxxxxxxxxxxxxx> schrieb im
Newsbeitrag news:%23vOxe8hqFHA.1444@xxxxxxxxxxxxxxxxxxxxxxx
> 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" <O.Braun@xxxxxxxxx> skrev i en meddelelse
> news:uxOdH$gqFHA.3352@xxxxxxxxxxxxxxxxxxxxxxx
>>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?
>>
>
>
.
- References:
- List SQL servers in a network
- From: Oliver Braun
- Re: List SQL servers in a network
- From: Benny S. Tordrup
- List SQL servers in a network
- Prev by Date: Re: Synchronization bool shared fields
- Next by Date: Re: How to tell if an interface method has been implemented
- Previous by thread: Re: List SQL servers in a network
- Next by thread: RE: List SQL servers in a network
- Index(es):
Relevant Pages
|
