| |
|
|
| SM0 2005 - Populating a list of SQL Servers |
| Author |
Jasper Smith |
Hits |
5786 |
| Create Date |
12-09-2004 |
Last Updated |
05-10-2008 |
| Versions |
SQL2005 |
|
|
Overview
In this article I'll look at a number of methods
for enumerating SQL Servers to produce a list. I'll also present a procedure
that can be used to populate a Windows Form combobox with a list of SQL Servers.
For information on how to build and compile a SMO application please review Getting
Started with SMO in SQL 2005.
Enumerating SQL Servers on the Network
As in SQL-DMO, SMO provides a method of enumerating
SQL Servers on the network. The EnumAvailableSqlServers method of the SmoApplication
object returns a DataTable with the Name column containing the full name of
the enumerated servers. In the example below we are using the overloaded method
that allows passing a boolean LocalOnly parameter that determines whether to
only enumerate local instances or not.
[Visual Basic]
Imports Microsoft.SqlServer.Management.Smo
Imports System.Data
Module SMOTest
Sub Main()
Dim dt As DataTable = SmoApplication.EnumAvailableSqlServers(False)
If dt.Rows.Count > 0 Then
For Each dr As DataRow in dt.Rows
Console.WriteLine(dr("Name"))
Next
End If
End Sub
End Module
[C#]
using System;
using System.Data;
using Microsoft.SqlServer.Management.Smo;
namespace SMOTest
{
class Program
{
static void Main()
{
DataTable dt = SmoApplication.EnumAvailableSqlServers(false);
if (dt.Rows.Count > 0)
{
foreach (DataRow dr in dt.Rows)
{
Console.WriteLine(dr["Name"]);
}
}
}
}
} |
Enumerating Local Instances
An alternative method when enumerating only
local instances is to check the registry key HKLM\SOFTWARE\Microsoft\Microsoft
SQL
Server for the InstalledInstances value as shown below.
[Visual Basic]
Imports Microsoft.Win32
Module SMOTest
Sub Main()
Dim rk As RegistryKey = Registry.LocalMachine.OpenSubKey("SOFTWARE\Microsoft\Microsoft SQL Server")
Dim instances As String() = CType(rk.GetValue("InstalledInstances"),String())
If (instances.Length > 0) Then
For Each element As String in instances
If element = "MSSQLSERVER" Then
Console.WriteLine(System.Environment.MachineName)
Else
Console.WriteLine(System.Environment.MachineName + "\" + element)
End If
Next
End If
End Sub
End Module
[C#]
using System;
using Microsoft.Win32;
namespace SMOTest
{
class Program
{
static void Main()
{
RegistryKey rk = Registry.LocalMachine.OpenSubKey(@"SOFTWARE\Microsoft\Microsoft SQL Server");
String[] instances = (String[])rk.GetValue("InstalledInstances");
if (instances.Length > 0)
{
foreach (String element in instances)
{
if (element == "MSSQLSERVER")
Console.WriteLine(System.Environment.MachineName);
else
Console.WriteLine(System.Environment.MachineName + @"\" + element);
}
}
}
}
} |
Enumerating Registered Servers
To enumerate servers registered in SQL Server
Management Studio we can use the EnumRegisteredServers method of the SqlServerRegistrations
class. One vast improvment over SQL-DMO is that there is no longer any need
to use recursion to loop through the servers and server groups.
[Visual Basic]
Imports Microsoft.SqlServer.Management.Smo.RegisteredServers
Imports Microsoft.SqlServer.Management.Smo
Module SMOTest
Sub Main()
Dim rsvrs as RegisteredServer() = SmoApplication.SqlServerRegistrations.EnumRegisteredServers()
Dim localserver As String = System.Environment.MachineName
For Each rs As RegisteredServer in rsvrs
Console.WriteLine(rs.ServerInstance.Replace(".",localserver) _
.Replace("(local)",localserver) _
.Replace("localhost",localserver))
Next
End Sub
End Module
[C#]
using System;
using Microsoft.SqlServer.Management.Smo;
using Microsoft.SqlServer.Management.Smo.RegisteredServers;
namespace SMOTest
{
class Program
{
static void Main()
{
RegisteredServer[] rsvrs = SmoApplication.SqlServerRegistrations.EnumRegisteredServers();
String localserver = System.Environment.MachineName;
foreach (RegisteredServer rs in rsvrs)
{
Console.WriteLine(rs.ServerInstance.Replace(".",localserver)
.Replace("(local)",localserver)
.Replace("localhost",localserver));
}
}
}
} |
C# GetServers Procedure
A common requirement is to populate a combobox
with a list of servers. Using a combination of the techniques demonstrated
already, the procedure below takes a ComboBox as a reference parameter and populates
it with a list of SQL Servers.
[C#]
private void GetServers(ref ComboBox cmbServers)
{
try
{
cmbServers.Items.Clear();
// SMO Enum Servers
DataTable dt = SmoApplication.EnumAvailableSqlServers(false);
if (dt.Rows.Count > 0)
{
// Load server names into combo box
foreach (DataRow dr in dt.Rows)
{
//only add if it doesn't exist
if (cmbServers.FindStringExact((String)dr["Name"]) == -1)
cmbServers.Items.Add(dr["Name"]);
}
}
//Registry for local
RegistryKey rk = Registry.LocalMachine.OpenSubKey(@"SOFTWARE\Microsoft\Microsoft SQL Server");
String[] instances = (String[])rk.GetValue("InstalledInstances");
if (instances.Length > 0)
{
foreach (String element in instances)
{
String name = "";
//only add if it doesn't exist
if (element == "MSSQLSERVER")
name = System.Environment.MachineName;
else
name = System.Environment.MachineName + @"\" + element;
if (cmbServers.FindStringExact(name) == -1)
cmbServers.Items.Add(name);
}
}
// Registered Servers
RegisteredServer[] rsvrs = SmoApplication.SqlServerRegistrations.EnumRegisteredServers();
foreach (RegisteredServer rs in rsvrs)
{
String name = "";
name = rs.ServerInstance.Replace(".", System.Environment.MachineName)
.Replace("(local)", System.Environment.MachineName)
.Replace("localhost", System.Environment.MachineName);
//only add if it doesn't exist
if (cmbServers.FindStringExact(name) == -1 && name.Length > 0)
cmbServers.Items.Add(name);
}
// Default to default instance on this machine
cmbServers.SelectedIndex = cmbServers.FindStringExact(System.Environment.MachineName);
// If this machine is not a SQL server
// then select the first server in the list
if (cmbServers.SelectedIndex < 0)
{
cmbServers.SelectedIndex = 0;
}
}
catch (SmoException ex)
{
if (this.Visible == false)
this.Show();
DisplayErrorMessage(ex);
}
catch (Exception ex)
{
if (this.Visible == false)
this.Show();
DisplayErrorMessage(ex);
}
}
// generic error display
private void DisplayErrorMessage(Exception ex)
{
string errorMessage;
errorMessage = ex.Message;
while (ex.InnerException != null)
{
errorMessage += Environment.NewLine + ex.InnerException.Message;
ex = ex.InnerException;
}
MessageBox.Show(this, errorMessage, this.Text + @" - Error",
MessageBoxButtons.OK, MessageBoxIcon.Error,
MessageBoxDefaultButton.Button1);
} |
Related Articles
 GettingStarted with SMO in SQL 2005
|
|
articles
