?
Solved

Enumerating Servers and databases on the server

Posted on 2006-06-22
7
Medium Priority
?
373 Views
Last Modified: 2010-04-23

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
Comment
Question by:alan_ITG
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
  • 2
7 Comments
 
LVL 96

Expert Comment

by:Bob Learned
ID: 16961889
1) .NET version?  2002, 2003 or 2005?

2) SQL Server versions?

Bob
0
 

Author Comment

by:alan_ITG
ID: 16966776

Hi Bob,

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

Thsnks,


Alan
0
 
LVL 2

Expert Comment

by:Mystify
ID: 16967939
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.

 
LVL 96

Accepted Solution

by:
Bob Learned earned 1000 total points
ID: 16970297
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
 
LVL 2

Expert Comment

by:Mystify
ID: 16970331
cool, even I will give it a look.

Old school or not, it works.  ;)
0
 
LVL 96

Expert Comment

by:Bob Learned
ID: 16971629
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
 

Author Comment

by:alan_ITG
ID: 16990487

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

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

Microsoft Reports are based on a report definition, which is an XML file that describes data and layout for the report, with a different extension. You can create a client-side report definition language (*.rdlc) file with Visual Studio, and build g…
It was really hard time for me to get the understanding of Delegates in C#. I went through many websites and articles but I found them very clumsy. After going through those sites, I noted down the points in a easy way so here I am sharing that unde…
In this video we outline the Physical Segments view of NetCrunch network monitor. By following this brief how-to video, you will be able to learn how NetCrunch visualizes your network, how granular is the information collected, as well as where to f…
How to fix incompatible JVM issue while installing Eclipse While installing Eclipse in windows, got one error like above and unable to proceed with the installation. This video describes how to successfully install Eclipse. How to solve incompa…
Suggested Courses

770 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