How to get the list of SqlInstances of a paricular machine

Question

Can anyone tell me how to get remote Sqlserver instances using c# and SMO or any api?

I have a remote server name "RemoteMC", which has 2 instances of sql server: "RemoteMc" and "RemoteMC\sqlexpress"

I try to get the instances in code like this:

Server srv=new Server("RemoteMC");
DataTable dt=SmoApplication.EnumAvailableSqlServer(true);

But it returns "Host\sqlexpress"

I don't know what went wrong. How can I get the result back as:

RemoteMC
RemoteMC\sqlexpress;

?

Answer 1

One way to do it is to read the registry entry ""Software\Microsoft\Microsoft SQL Server\Instance Names\SQL\" under Local Machine. Are you allowed to read registry in the your solution?

Answer 2

The SmoApplication.EnumAvailableSqlServers method is what you're looking for. There are 3 overloads, and one of those takes a string parameter for the server name.

It returns a DataTable whose rows have fields like Version, name, IsLocal, etc.

You'll need to add a reference to the SMO libraries, and you'll probably want to have "using Microsoft.SqlServer.Management.Smo;" at the top of your C# file.

See http://www.sqldbatips.com/showarticle.asp?ID=34 for an intro to SQL SMO.

EDIT: Some code to address this particular problem (I have not compiled or run this):

DataTable dataTable = SmoApplication.EnumAvailableSqlServers(false);

foreach (DataRow dataRow in dataTable)
{
    if ((dataRow["Server"] as string) == "MyServerName")
        Console.WriteLine(dataRow["Instance"] as string);
}

Answer 3

Use the builtin way.

System.Data.Sql.SqlDataSourceEnumerator

Answer 4

hi,

EnumAvailableServers(false) is returning data table with zero records i.e not listing any instance on a sql 2008 cluster.......what could be the reason???