Iterating my Database Servers

[John Wright]

I have the following code that I run to get a list of all the databases and
instances so I can connect to them:

Dim listOfServers As New List(Of String)()
'create a new instance of our SqlDataSourceEnumerator
Dim sqlEnumerator As SqlDataSourceEnumerator =
SqlDataSourceEnumerator.Instance
'get the datatable containing our sql servers
Dim sqlServersTable As DataTable = sqlEnumerator.GetDataSources()
'iterate thru all the rows
For Each rowOfData As DataRow In sqlServersTable.Rows
'get the server name
Dim serverName As String = rowOfData("ServerName").ToString()
'get the instance name
Dim instanceName As String =
rowOfData("InstanceName").ToString()

'check if the instance name is empty
If Not instanceName.Equals(String.Empty) Then
'append the instance name to the server name
serverName += String.Format("\{0}", instanceName)
End If

'add the server to our list
listOfServers.Add(serverName)
Next

'sort the list if the sort option is specified
If shouldSortList Then
'sort it!
listOfServers.Sort()
End If
'return our list
Return listOfServers

Lately, when I run this code all the instance names are empty. This code
used to work. I could load the instance names in a list and then select them
to create a dynamic connection string to connect and now all the value in the
"InstanceName" are null. Anyone know why?

John
Was this post helpful to you?

 

[Cowboy \(Gregory A. Beamer\)]

have the admins cahnged anything. One possibility is setting up the SQL
Servers to respond to a port other than 1433, as an example. This is
commonly done for security reasons. This would stop you from being able to
see the servers.

BTW, another method of iterating servers is SMO:
http://devpinoy.org/blogs/misterclay/archive/2006/06/09/3337.aspx

It has many of the same caveats mentioned above.

--
Gregory A. Beamer
MVP; MCP: +I, Se, SD, DBA

*************************************************
| Think outside the box! |
*************************************************