• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 389
  • Last Modified:

Enumerating Servers and databases on the server


Hi,

I want to list all servers available on a network, select a server, then list all the SQL Server databases on the selected server.

I can list the servers available on the network but am struggling to list all the databases on the selected server.

Any ideas? Code would be very useful!

Regards,

Alan
0
alan_ITG
Asked:
alan_ITG
  • 3
  • 2
  • 2
1 Solution
 
Bob LearnedCommented:
1) .NET version?  2002, 2003 or 2005?

2) SQL Server versions?

Bob
0
 
alan_ITGAuthor Commented:

Hi Bob,

I am using VS 2005 and SQL Server 2000 & 2005 are the target systems.

Thsnks,


Alan
0
 
MystifyCommented:
To find all the servers, it requires 2 things.

1. You need to add a project reference to SQLDMO
2. The SQL Server Browser service must be running. This is the service that allows it to be detected, without it running, the server will not be found on the network.




'Find SQL Servers on network:

        Dim Names As SQLDMO.NameList
        Dim SQLApp As SQLDMO.Application

        SQLApp = New SQLDMO.Application

        If My.Computer.Network.IsAvailable = False Then exit sub ' or do whatever you want to do if your network is not available.

        Names = SQLApp.ListAvailableSQLServers()

Now all the found servers will be in the Names object and you can loop through the array and get the names by Names.Item(Index #)
I have not been able to find a way to find SQL Servers that are not running their browser, nor have I been able to find servers without using SQLDMO

I initially thought about enumerating all the computers on a network and trying to connect, only do find that the datasource could use a PCNAME/SERVICENAME format, and the servicename can be anything, and I do not know how to get that manually. SQLDMO was the best solution.



To list all the databases on the server, you first must connect to it.
I am going to assume that you know how to do that.

Anyway, to list the databases just run the following query:

"SELECT * FROM master..sysdatabases"



0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
Bob LearnedCommented:
That is why I ask about .NET version.  That way you can get the "best" advice.

SQLDMO is old school.  SMO is new technology that comes with 2005.  

Here is a class to describe what I mean:

' Reference:
'    A Quick Introduction to SQL Server Management Objects (SMO)  
'      http://www.yukonxml.com/articles/smo/

' Add a reference to Microsoft.SqlServer.Smo and Microsoft.SqlServer.ConnectionInfo to the project for the following imports
Imports Microsoft.SqlServer.Management
Imports Microsoft.SqlServer.Management.Common
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

Bob
0
 
MystifyCommented:
cool, even I will give it a look.

Old school or not, it works.  ;)
0
 
Bob LearnedCommented:
Actually, I am not saying that old school doesn't work, it's just that once you start looking at SMO, you'll see a considerable amount more than SQL-DMO.  Enumerating servers is only the tip of the iceberg, and a good place to start.

Bob
0
 
alan_ITGAuthor Commented:

Thanks all for the assistance!

Mistify - thanks for the help.  I decided to award the points to TheLearnedOne for the completeness of the solution.

regards,


Alan
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

  • 3
  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now