Enumerating SQL Servers using C#
SQL-DMO (SQL Server Distributed Management Objects) lets you programmatically get a list of SQL Servers in the network.
1. Ensure you have the latest SQL Server Service Pack. You could find out which version you are using by doing a SELECT @@VERSION
2. In your C# app, add a reference to sqldmo.dll (You need to select the COM tab when adding a reference)
3. Add the namespace
using SQLDMO;
4. The code that discovers the SQL Servers is:
SQLDMO.Application sqlApp = new SQLDMO.ApplicationClass();
SQLDMO.NameList sqlServers = sqlApp.ListAvailableSQLServers();
//Note that in COM, index starts from 1
long lCount = sqlServers.Count;
for (int i=1; i <= lCount; i++)
{
string str = sqlServers.Item(i);
Console.WriteLine(str);
}
5. Execute the application and you would see a list of SQL Servers. You could connect to a SQL Server by creating an instance of SQLServerClass as below:
SQLDMO.SQLServer mysqlserver = new SQLDMO.SQLServerClass();
mysqlserver.Connect("name of the server","sa","password");
Console.WriteLine("Connected...");