asked on

Enumerate SQL Servers

Hi there,
I'm trying to populate a combo box with a list of SQL servers.
When the server has been selected I'm looking to move on then produce a list
of databases within that database.
Basically I'm getting all the info together to create a connectiion string.
I'm using VB2008 and I need to be able to connect using .net framework 2 to SQL 2000,2005,2008.

I've found this code on EE but frankly it is a bit beyond me.

Can someone help by showing me how to use it correctly or suggest an alternative.
Thanks in advance
Paul

Public Class SqlServer
 
        ''' <summary>
        ''' The form of EnumerateServers for all machines in the network.
        ''' </summary>
        Public Overloads Shared Function EnumerateServers() As ServerInstance()
            Return EnumerateServers("")
        End Function
 
        ''' <summary>
        ''' Return a collection of server instance descriptors for all SQL Servers within a network
        ''' </summary>
        ''' <param name="computerName">Specify a computer name to target a particular machine</param>
        ''' <returns>An array of ServerInstance descriptor objects</returns>
        ''' <remarks>This method translates the DataTable to a list of objects with Intellisense.</remarks>
        Public Overloads Shared Function EnumerateServers(ByVal computerName As String) As ServerInstance()
 
            Dim tableServers As DataTable = Nothing
 
            If computerName.Length = 0 Then
                tableServers = SmoApplication.EnumAvailableSqlServers()
            Else
                tableServers = SmoApplication.EnumAvailableSqlServers(computerName)
            End If
 
            ' Create enough space for all the SQL Server instances.
            Dim list(tableServers.Rows.Count - 1) As ServerInstance
 
            ' Build the list of servers.
            For index As Integer = 0 To tableServers.Rows.Count - 1
 
                Dim row As DataRow = tableServers.Rows(index)
 
                Dim name As String = row("Name").ToString()
                Dim server As String = row("Server").ToString()
                Dim instance As String = row("Instance").ToString()
                Dim clustered As Boolean = row("IsClustered").ToString()
                Dim local As Boolean = row("IsLocal").ToString()
 
                Dim entry As New ServerInstance(name, server, instance, clustered, local)
 
                list(index) = entry
 
            Next index
 
            Return list
        End Function
 
        Public Class ServerInstance
 
            Public Sub New(ByVal name As String, ByVal server As String, ByVal instance As String, ByVal clustered As Boolean, ByVal local As Boolean)
                m_name = name
                m_server = server
                m_instance = instance
                m_clustered = clustered
                m_local = local
            End Sub
 
            Private m_name As String = ""
            Public Property Name() As String
                Get
                    Return m_name
                End Get
                Set(ByVal value As String)
                    m_name = value
                End Set
            End Property
 
            Private m_server As String = ""
            Public Property Server() As String
                Get
                    Return m_server
                End Get
                Set(ByVal value As String)
                    m_server = value
                End Set
            End Property
 
            Private m_instance As String = ""
            Public Property Instance() As String
                Get
                    Return m_instance
                End Get
                Set(ByVal value As String)
                    m_instance = value
                End Set
            End Property
 
            Private m_clustered As Boolean
            Public Property IsClustered() As Boolean
                Get
                    Return m_clustered
                End Get
                Set(ByVal value As Boolean)
                    m_clustered = value
                End Set
            End Property
 
            Private m_local As Boolean
            Public Property IsLocal() As Boolean
                Get
                    Return m_local
                End Get
                Set(ByVal value As Boolean)
                    m_local = value
                End Set
            End Property
        End Class
 
    End Class

Open in new window

ASKER CERTIFIED SOLUTION

Dirk Haest

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

pivar

Hi,

For SmoApplication, I think you need the SQL server tools installed. Here two other ways to accomplish what you require:

OleDbDataReader reader =
OleDbEnumerator.GetEnumerator(Type.GetTypeFromProgID("MSDASQL Enumerator"));
// OleDbEnumerator.GetEnumerator(Type.GetTypeFromProgID("MSDAENUM, MSDASQL Enumerator, SQLNCLI Enumerator, SQLOLEDB Enumerator"));

while (reader.Read()) {
for (int i = 0; i < reader.FieldCount; i++) {
Console.WriteLine("{0} = {1}", reader.GetName(i), reader.GetValue(i));
}
Console.WriteLine("==================================");
}

Console.WriteLine("Press any key to continue.");
Console.ReadKey();

or

// Retrieve the enumerator instance and then the data.
SqlDataSourceEnumerator instance =
SqlDataSourceEnumerator.Instance;
System.Data.DataTable table = instance.GetDataSources();

// Display the contents of the table.
DisplayData(table);

Console.WriteLine("Press any key to continue.");
Console.ReadKey();
}

private static void DisplayData(System.Data.DataTable table) {
foreach (System.Data.DataRow row in table.Rows) {
foreach (System.Data.DataColumn col in table.Columns) {
Console.WriteLine("{0} = {1}", col.ColumnName, row[col]);
}
Console.WriteLine("============================");
}

/peter

pivar

I'm sorry, first example should read:

OleDbDataReader reader =
OleDbEnumerator.GetEnumerator(Type.GetTypeFromProgID("SQLOLEDB Enumerator"));
// OleDbEnumerator.GetEnumerator(Type.GetTypeFromProgID("MSDAENUM, MSDASQL Enumerator, SQLNCLI Enumerator, SQLOLEDB Enumerator"));

while (reader.Read()) {
for (int i = 0; i < reader.FieldCount; i++) {
Console.WriteLine("{0} = {1}", reader.GetName(i), reader.GetValue(i));
}
Console.WriteLine("==================================");
}

Console.WriteLine("Press any key to continue.");
Console.ReadKey();

kewlchap_85

Check the code:

Dim str As String
        Dim instance As SqlDataSourceEnumerator = SqlDataSourceEnumerator.Instance
        Dim table As System.Data.DataTable = instance.GetDataSources()
        For Each row As DataRow In table.Rows
            If (row(1).ToString <> "") Then
                str = "\" & row(1)
            Else
                str = ""
            End If
            Cmb_server.Items.Add(row(0) & str)
        Next

Open in new window

PaulEll

ASKER

Thanks everyone for you speedy help.

The first answer was correct

Thanks again - You have saved me loads of time.

Paul