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;
?
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):
EDIT:.Rows add to foreach so that it can compile.
DataTable dataTable = SmoApplication.EnumAvailableSqlServers(false);
foreach (DataRow dataRow in dataTable.Rows)
{
if ((dataRow["Server"] as string) == "MyServerName")
Console.WriteLine(dataRow["Instance"] as string);
}
Use the builtin way.
System.Data.Sql.SqlDataSourceEnumerator
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?