List registered SQL servers using C#

2011/10/16 21:35

Applicable to: Smart Marine 3D, Smart Plant 3D using SQL 2005 client

Smart Plant/Marine 3D software require the SQL servers where projects/plants database is located to be registered on each client workstation. In the software version up to 9.1 HotFix: ANY it is required to have also SQL 2005 client installed on the workstation.

Writing an external add-on software that need to access SQL database (relax - access it read only, creating some kind of reports for example) it would be useful to get a list of registered servers available to the particular client where the software will be used.

An easy way is to use Microsoft.SqlServer.Management.Smo namespace. Example code follows.

  1. using Microsoft.SqlServer.Management;using Microsoft.SqlServer.Management.Smo;using Microsoft.SqlServer.Management.Smo.RegisteredServers;private void Form1_Load(object sender, EventArgs e) { RegisteredServer[] rsvrs = SqlServerRegistrations.EnumRegisteredServers(); String localserver = System.Environment.MachineName; foreach (RegisteredServer rs in rsvrs) { string svrname = rs.ServerInstance.Replace(".", localserver) .Replace("(local)", localserver) .Replace("localhost", localserver);// Add SQL servers name to checked list box controlif (cklbServers.CheckedItems.Contains(svrname) == false) cklbServers.Items.Add(svrname, CheckState.Unchecked); } }

To work the above code needs the following DLL to be registered in the C# project

Location: C:\Program Files\Microsoft SQL Server\100\SDK\Assemblies

C# project references

[caption id="attachment_142" align="alignnone" width="300" caption="C# project References for enumerating registered SQL servers"][/caption]