How to list available instances of SQL Servers using SMO in C#?

Question

Can anybody explain me what I wrong I am doing in the following piece of code:

DataTable dt=SmoApplication.EnumAvailableSqlServer(true);
Server sr = new Server("Test");

foreach(DataBase db in sr.DataBases)
{
    Console.WriteLine(db["name"]);
}

It gives an exception in sr.Databases that can not be connected.

Winniewinnifred

Could be that your firewall (or something else) blocks osql's broadcast which tries to find available instances? – Metzgar

Why are you enumerating the SQL Servers, and then still instantiating just the one "TEST" one?? – Sylviasylviculture

Answer 1

Take a look at the following links they may be helpful:

• Enumerate SQL Server Instances in C#, Using ODBC
• How to get a list of available SQL Servers using C# (MSDN)
• Populating a list of SQL Servers

Alternatively you could change your code to this:

DataTable dt = SmoApplication.EnumAvailableSqlServers(false);
if (dt.Rows.Count > 0)
{
    foreach (DataRow dr in dt.Rows)
    {
        Console.WriteLine(dr["Name"]);
    }
}

Hope this solves your problem.

Oujda

The question says that he/she needs to find the instance names. But the code he/she gave is for finding the databases in a particular SQLServer instance. – Tran

The question (not the unrelated answer) brought me here, so IMHO this is the best answer, and I'll add this: if you are looking for SmoApplication, this worked best for me: GitHub's "Unofficial.Sql.Server.Management.Objects. – Afroasiatic

The above code gives me only network servers. what if i want both local and network servers. – Rattlebrained

Answer 2

Do you have a SQL Server with the instance name Test? If not, that is your problem.

It looks like you are trying to enumerate all of the local SQL Server instances. If so, this code will work:

DataTable dt = SmoApplication.EnumAvailableSqlServers(true);

foreach (DataRow dr in dt.Rows)
{
    Console.WriteLine(dr["Name"]);
    Console.WriteLine("   " + dr["Server"]);
    Console.WriteLine("   " + dr["Instance"]);
    Console.WriteLine("   " + dr["Version"]);
    Console.WriteLine("   " + dr["IsLocal"]);
}

Jeggar

The above code gives me only network servers. what if i want both local and network servers. – Rattlebrained

Answer 3

Just in case the question is titled wrong i.e. he wants to find the databases in the particular instance:

using System;
using Microsoft.SqlServer.Management.Smo;
using System.Data;
using System.Windows.Forms;
namespace ConsoleApplication1
{
    class Program
    {
        static void Main()
        {
            Server sr = new Server("MACHINE_NAME\\INSTANCE_NAME");

            try
            {
                foreach (Database db in sr.Databases)
                {
                    Console.WriteLine(db.Name);
                }
                Console.Read();
            }
            catch (Exception Ex)
            {
                MessageBox.Show(Ex.ToString());
            }
        }
    }
}

Else Lucas Aardvark answer is most appropriate.

Tran

You don't need the call to SmoApplication.EnumAvailableSqlServers as the return value is not used. – Jeggar

Answer 4

using Microsoft.Win32;

       RegistryKey rk = Registry.LocalMachine.OpenSubKey(@"SOFTWARE\Microsoft\Microsoft SQL Server");
        String[] instances = (String[])rk.GetValue("InstalledInstances");
        if (instances.Length > 0)
        {
            foreach (String element in instances)
            {
               Console.WriteLine(element);    // element is your server name                
            }
        }

Kidron

this will only return 32bit servers if compiled as 32bit app and the same for 64bit – Cristen

is it possible to get the installed instances without accessing registry ? – Rattlebrained