5

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.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459

4 Answers4

10

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

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.

Kredns
  • 36,461
  • 52
  • 152
  • 203
  • 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. – Ganesh R. Jul 15 '09 at 10:45
  • 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. – batpox Feb 12 '19 at 15:20
  • The above code gives me only network servers. what if i want both local and network servers. – Uday Teja Jul 10 '19 at 07:17
6

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"]);
}
adrianbanks
  • 81,306
  • 22
  • 176
  • 206
1

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.

Ganesh R.
  • 4,337
  • 3
  • 30
  • 46
1
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                
            }
        }
Xorsat
  • 2,388
  • 21
  • 21