Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Enumerating Servers and databases on the server

Posted on 2006-06-22
7
Medium Priority
?
381 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
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.

 
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

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

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

It’s quite interesting for me as I worked with Excel using vb.net for some time. Here are some topics which I know want to share with others whom this might help. First of all if you are working with Excel then you need to Download the Following …
Calculating holidays and working days is a function that is often needed yet it is not one found within the Framework. This article presents one approach to building a working-day calculator for use in .NET.
In this video you will find out how to export Office 365 mailboxes using the built in eDiscovery tool. Bear in mind that although this method might be useful in some cases, using PST files as Office 365 backup is troublesome in a long run (more on t…
Sometimes it takes a new vantage point, apart from our everyday security practices, to truly see our Active Directory (AD) vulnerabilities. We get used to implementing the same techniques and checking the same areas for a breach. This pattern can re…

609 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