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.

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]