[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Enumerate SQL Servers

Posted on 2009-02-19
5
Medium Priority
?
991 Views
Last Modified: 2013-11-25
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

0
Comment
Question by:PaulEll
5 Comments
 
LVL 53

Accepted Solution

by:
Dhaest earned 2000 total points
ID: 23679486
Step by step articles:
Programmatically List All SQL Servers And Their Databases Using SMO
http://devpinoy.org/blogs/keithrull/archive/2006/06/08/Programmatically-List-All-SQL-Servers-And-Their-Databases-Using-SMO-.aspx

Find Available SQL Server Using SMO Classes in Visual Basic .NET 2005 Application
http://www.tek-tips.com/faqs.cfm?fid=6142
0
 
LVL 22

Expert Comment

by:pivar
ID: 23679494
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




0
 
LVL 22

Expert Comment

by:pivar
ID: 23679531
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();


0
 
LVL 2

Expert Comment

by:kewlchap_85
ID: 23683716
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

0
 
LVL 1

Author Closing Comment

by:PaulEll
ID: 31548671
Thanks everyone for you speedy help.

The first answer was correct

Thanks again - You have saved me loads of time.

Paul
0

Featured Post

Fill in the form and get your FREE NFR key NOW!

Veeam is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
This is Part 3 in a 3-part series on Experts Exchange to discuss error handling in VBA code written for Excel. Part 1 of this series discussed basic error handling code using VBA. http://www.experts-exchange.com/videos/1478/Excel-Error-Handlin…
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an anti-spam), the admin…

830 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question