List All SQL Servers with vb.net

At times at work it is necessary to show a list of SQL Servers to users. So this is the code I came up with. Hope it helps someone!

(FIRST —> Add the .Net reference to your project "Microsoft.SQLServer.SMO")
Imports Microsoft.SqlServer.Management
Imports Microsoft.SqlServer.Management.Smo
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
Then in your routine that populates your list:
        Dim s() As SqlServer.ServerInstance
        s = SqlServer.EnumerateServers()
        cmbServer.Items.Clear()
        For i = 0 To s.Length – 1
            cmbServer.Items.Add(s(i).Name)
        Next

Technorati Tags: ,,,,,,,,,,,,,,

Windows Live Tags: vb.net,.NET Framework,csharp,SMO,SQL,List,Servers,Microsoft,SQLServer,Management,EnumerateServers,ServerInstance,collection,SmoApplication,EnumAvailableSqlServers

Advertisements
  1. #1 by Anton on October 27, 2009 - 7:45 am

    It DID help a LOT! Thanks very much :o)

  2. #2 by Scott on February 16, 2010 - 1:52 pm

    Dim tableServers As DataTable = NothingSystem.Data.DataTable in case someone else has an ambig.

  3. #3 by Ghadah on March 16, 2010 - 5:31 am

    Thanks a lot for a great code !

  4. #4 by Unknown on May 30, 2010 - 6:09 am

    This blog is very informative. I love blogging when it is for or about something I believe in. I also read news blogs often and find that it makes me feel more intelligent every time I read them. I also feel like I am a pretty good person who tries to treat others with respect, no matter what their opinion is. There are some real haters out there. Thanks again. I learned a few things about blogging. I will definitely put your site on my speed dial.Thankshttp://www.vanjobb.huhttp://www.vanjobb.hu/mobilinternet.php?11http://www.vanjobb.hu/Bankbetetkamatok.php?22http://www.vanjobb.hu/nevnapokdatumszerint.php?39http://www.vanjobb.hu/nevnapoknaptar.php?41http://www.vanjobb.hu/nevnapok.php?40

  5. #5 by AndyF on February 24, 2011 - 5:37 pm

    You sure did write a lot of code to do something that could be done much more simply… How about…

    Imports Microsoft.SqlServer.Management.Smo
    ….
    Dim _Servers As New ArrayList
    Try
    Dim dtlSQLServers As DataTable
    dtlSQLServers = SmoApplication.EnumAvailableSqlServers(False)
    For Each drServer As DataRow In dtlSQLServers.Rows
    _Servers.Add(drServer(“Name”))
    Next
    _Servers.Sort()
    Return _Servers
    Catch ex As Exception
    MsgBox(ex.Message, MsgBoxStyle.Critical, “Problem!”)
    Return _Servers
    End Try

    This has worked in my code for a long long time. Of course, naturally, Microsoft screwed this all up with the release of SQL 2008, and on 2008 this same code will produce an error. They will then tell you to load the SQL 2005 Backward Compatibility Pack – which does NOTHING – you still get the same error. I currently have a support ticket open with those [dont want to use bad words here] ‘people’ at Microsoft and when they figure out how to unscrew their screw up, I will return here and post the solution…

    …otherwise, this code runs great for SQL 2005, and has for years. (Which some say, is why Microsoft simply had to screw that up… You know their motto: “Where DONT you want to go today?”

  6. #6 by David Cataldo on November 11, 2011 - 11:39 am

    Thank you! It worked great!

  7. #7 by Simon Barnett on October 7, 2012 - 7:36 am

    Good stuff! 🙂 I modified it just slightly like this:

    Public Function EnumerateServers(Optional ByVal computerName As String = “”) As List(Of ServerInstance)
    Dim tableServers As DataTable = Nothing
    Dim slist As New List(Of ServerInstance)
    If computerName.Length = 0 Then
    tableServers = SmoApplication.EnumAvailableSqlServers
    Else
    tableServers = SmoApplication.EnumAvailableSqlServers(computerName)
    End If
    ‘ Build the list of servers.
    For Each row As System.Data.DataRow In tableServers.Rows
    slist.Add( _
    New ServerInstance( _
    row(“Name”).ToString(), _
    row(“Server”).ToString(), _
    row(“Instance”).ToString(), _
    row(“IsClustered”).ToString(), _
    row(“IsLocal”).ToString() _
    ) _
    )
    Next
    Return slist
    End Function

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: