Results 1 to 7 of 7
  1. #1
    3 Star Lounger
    Join Date
    Aug 2001
    Location
    Jeddah, Saudi Arabia
    Posts
    243
    Thanks
    0
    Thanked 1 Time in 1 Post

    Enumerating SQL Servers (VB .NET 2003)

    Can anybody point me in the direction of how to enumerate the SQL Server server groups and then the databases in each server group and then the tables within each database for SQL sever groups visible to my computer?

    I have a request for an application that will be used to create the tables required for another application using Oracle, SQL server or Access. Oracle and Access I can do but for the life of me I can't enumerate these SQL Server collections.

    There is a ServerGroups collection of the SQLDOM.Application object but the help says it enumerates a list held in the registry. I wrote a test application and every time I run the program the ServerGroups.Count property comes back as zero. I can see our Server Groups in the Enterprise Manager so they do exist.

    We are using SQL Server 2000.

    Regards,
    Kevin Bell

  2. #2
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Enumerating SQL Servers (VB .NET 2003)

    Kevin,
    Have you tried something like:<pre> Dim sqlApp As New SQLDMO.Application()
    Dim NL As SQLDMO.NameList, index As Integer

    NL = sqlApp.ListAvailableSQLServers
    For index = 1 To NL.Count
    ComboBox1.Items.Add(NL.Item(index))
    Next
    </pre>


    Later edit:
    or for a full enumeration, something like:
    <pre> Dim sqlApp As New SQLDMO.Application()
    Dim svrSQL As New SQLDMO.SQLServer()
    Dim dbs As SQLDMO.Database, tbl As SQLDMO.Table
    Dim NL As SQLDMO.NameList, index As Integer

    NL = sqlApp.ListAvailableSQLServers
    For index = 1 To NL.Count
    Try
    With svrSQL
    .LoginSecure = True
    .AutoReConnect = False
    .Connect(NL.Item(index))
    End With
    For Each dbs In svrSQL.Databases
    For Each tbl In dbs.Tables
    ListBox1.Items.Add(svrSQL.Name & ":" & dbs.Name & ":" & tbl.Name)
    Next
    Next
    Catch ex As System.Exception
    Exit Try
    Finally
    svrSQL.DisConnect()
    End Try
    Next
    svrSQL = Nothing
    </pre>

    Regards,
    Rory

    Microsoft MVP - Excel

  3. #3
    3 Star Lounger
    Join Date
    Aug 2001
    Location
    Jeddah, Saudi Arabia
    Posts
    243
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Enumerating SQL Servers (VB .NET 2003)

    Thanks for the code rory. I wanted to use a Tree View like the one found in the Enterprise Manager to display a list of available servers in Server Groups. I have since found out that the concept of a Server Group in SQL Server is a local thing stored in the registry on the local computer so that idea went out the window. I have used code based on yours to display a list of available servers. Not as nice as the view in the Enterprise Manager but it will do.

    Many thanks again.

    Regards,
    Kevin Bell

  4. #4
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Enumerating SQL Servers (VB .NET 2003)

    Hi Kevin,
    I must admit I had never really thought about the server groups before but, as you say, they are a local thing. I guess you could still use your tree view - you would just need to add in the functionality to add server groups at the top level as in Enterprise Manager and then add something to the current code to check if groups exist.
    Regards,
    Rory

    Microsoft MVP - Excel

  5. #5
    3 Star Lounger
    Join Date
    Aug 2001
    Location
    Jeddah, Saudi Arabia
    Posts
    243
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Enumerating SQL Servers (VB .NET 2003)

    Thanks again Rory. Now, how do I enumerate available Oracle servers?

    Regards,
    Kevin Bell

  6. #6
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Enumerating SQL Servers (VB .NET 2003)

    To be honest, I don't know if you can. As I recall, the Oracle client installation doesn't have that ability, you have to manually type in the database name (it may have changed in recent versions). If you know what server(s) are running Oracle, you could probably search them for INIT*.ORA files, where the asterisk would represent the db name, but then you would need code to configure the client to connect to those databases. Otherwise you could just parse the local tnsnames.ora file to get the preconfigured databases?
    Regards,
    Rory

    Microsoft MVP - Excel

  7. #7
    3 Star Lounger
    Join Date
    Aug 2001
    Location
    Jeddah, Saudi Arabia
    Posts
    243
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Enumerating SQL Servers (VB .NET 2003)

    I have figured out a way to get a list of configured Oracle servers. It depends on the Oracle client being installed on the local computer but from what I have found, you can't use any of the Oracle provided tools if it isn't.

    There is a registry key HKLMSOFTWAREORACLE with a key called ORACLE_HOME that holds the path to the home folder for Oracle. In this folder there is a subfolder called Network and within that a subfolder called Admin. In this folder there is a file called TNSNAMES.ORA.

    If this key or file doesn't exist then another key HKLMSOFTWAREORACLEALL_HOMESID0 and a key of PATH points to an alternate location where subfolders of NET80 and Admin should exist.

    Naturally if neither of these keys exist or the files are not found in these folders then you cannot procede.

    The TNSNAMES.ORA file holds descriptions of those Oracle servers that are configured. The syntax is quite archane. I have attached a VB .NET class that will locate and parse the file, returning a string array holding the names of the Oracle servers.

    This works just fine on my system here. We only have 2 Oracle servers and don't do anything fancy with them, like replication or clustering so I can't test all possible configurations.

    I hope this helps anybody who is interested.

    Regards,
    Kevin Bell

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •