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

Question

Hi

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.

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.

Answer 2

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

Answer 3

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"]);
}

Answer 4

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.

Answer 5

Hi buddy im facing a problm in accessing sql server instances

DataTable dt = SmoApplication.EnumAvailableSqlServers(false);

this is the code i m using but the problm is it is not geting server name in ma pc but the same project on another works perfect without any change

Answer 6

no man i checked this with turn off settings it is not working