Listing available SQL Servers in a network/pc

[yoavyoavyoav]

Hi There ,


I wanted to know if there's an easy way to list all available SQL Servers Names in my network/pc from a .net C# application ?

Thanks in advance.

[camel]

Use SQL Server Management Objects (or SQLDMO if pre SQL 2005), here is method that uses SMO:

 
        public static string[] EnumerateServers()
        {
            ArrayList colServers = new ArrayList();
            string[] arrServers = null;
 
            // Servers registered in SQL Management Studio
            Microsoft.SqlServer.Management.Smo.RegisteredServers.RegisteredServer[] rsvrs = SmoApplication.SqlServerRegistrations.EnumRegisteredServers();
            if (rsvrs != null)
            {
                foreach (Microsoft.SqlServer.Management.Smo.RegisteredServers.RegisteredServer rs in rsvrs)
                {
                    String name = "";
 
                    name = rs.Name.Replace(".", System.Environment.MachineName)
                                        .Replace("(local)", System.Environment.MachineName)
                                        .Replace("localhost", System.Environment.MachineName);
 
                    if (name.Length > 0 && colServers.IndexOf(name.ToUpper()) < 0)
                    {
                        colServers.Add(name.ToUpper());
                    }
                }
            }
 
            // Auto-Detect Network Servers
            DataTable dt = SmoApplication.EnumAvailableSqlServers(false);
            if (dt.Rows.Count > 0)
            {
                foreach (DataRow dr in dt.Rows)
                {
                    if (colServers.IndexOf(dr["Name"].ToString().ToUpper()) < 0)
                    {
                        colServers.Add(dr["Name"].ToString().ToUpper());
                    }
                }
            }
 
 
            // Local Installations (if machine call is running on is a SQL Server)
            Microsoft.Win32.RegistryKey rk = Microsoft.Win32.Registry.LocalMachine.OpenSubKey(@"SOFTWARE\Microsoft\Microsoft SQL Server");
            if (rk != null)
            {
                String[] instances = (String[])rk.GetValue("InstalledInstances");
                if (instances!=null && instances.Length > 0)
                {
                    foreach (String element in instances)
                    {
                        String name = "";
                        //only add if it doesn't exist
                        if (element == "MSSQLSERVER")
                            name = System.Environment.MachineName;
                        else
                            name = System.Environment.MachineName + @"\" + element;
 
                        if (colServers.IndexOf(name.ToUpper()) < 0)
                        {
                            colServers.Add(name.ToUpper());
                        }
                    }
                }
            }
 
            if (colServers.Count > 0)
            {
                arrServers = (string[])colServers.ToArray(typeof(string));
            }
 
            return arrServers;
        }
 
 
 

[darthdart]

hello good day... i'm new here.. i wanted to post a new topic but there was a similar topic but it new posts were no longer allowed.. and this was the nearest topic i could find... SQL.DMO does the work... in fact it gets the job done fast and easy... the problem is.. it doesn't work when there is a firewall.. and I'm using C# and when i try to get a list of servers running all i get is a blank on the combo box...and the other guys just wouldn't turn off the firewall because they think it is necessary to have one.. so i'm the one who's supposed to adjust I have successfully got a list of servers using the system registry by extracting the data from the Keys by using Microsoft.Win32.. but sometimes funny things happen when there are new servers.. We were hoping to able to get the list of servers within the network but because this is the only method I know that works even when the firewall was turned on.. then I guess I'll have to settle with it.. unless of course anybody out there has a better suggestion... anyone?

[saidfathi]

what this:ArrayList colServers = new ArrayList();?
what mean ArrayList?