Enumerating SQL Servers (VB .NET 2003)

[BigKev]

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

[rory]

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>

[BigKev]

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

[rory]

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.

[BigKev]

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

Regards,
Kevin Bell

[rory]

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?

[BigKev]

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