« Question of the Day: Really Disconnected Systems | Main | Upcoming ADO.NET 2.0 Workshop--Live! »

Question of the Day: Enumerating SQL Server Instances

Another question came up in the newsgroups today that's been asked (and answered) before so to short-circuit having to repeat myself, here is a blocl of code that can be used to enumerate the providers on a system and the services that the provider can see on the network. In other words, it lists the SQL Server instances on the network and puts the list in a DataGridView.

This code is extracted from an example in my new book Hitchhiker's Guide to Visual Studio and SQL Server due to be on the streets in early November. See www.hitchhikerguides.net for more information.

Private Sub ShowServerInstances()
' List Providers
Dim tblProviders As Data.DataTable = DbProviderFactories.GetFactoryClasses
DataGridView2.DataSource = tblProviders
lblStatus.Text = "Located providers... searching for servers."
lblStatus.Refresh()
' List Server Instances for a selected provider
If Not My.Computer.Network.IsAvailable Then _
MessageBox.Show("Local area network not enabled... only local instances visible")
Dim factory As DbProviderFactory = DbProviderFactories.GetFactory("System.Data.SqlClient")
Dim dsE As DbDataSourceEnumerator = factory.CreateDataSourceEnumerator
Dim dtInstances As DataTable = dsE.GetDataSources
DataGridView1.DataSource = AddServiceName(dtInstances)
End Sub
Shared Function AddServiceName(ByVal dtList As DataTable) As DataTable
If dtList Is Nothing Then Return Nothing
Dim colService As New DataColumn("ServiceName", GetType(String))
dtList.Columns.Add(colService)
Dim colState As New DataColumn("Status", GetType(String))
dtList.Columns.Add(colState)
For Each dr As DataRow In dtList.Rows
dr("ServiceName") = DBNull.Value
If Not IsDBNull(dr("Version")) Then
dr("ServiceName") = "MSSQL$" & dr("InstanceName")
Else
dr("ServiceName") = "MSSQLSERVER"
End If
dr("Status") = GetStatusForService(dr("ServiceName"), dr("ServerName"))
Next
Return dtList
End Function

TrackBack

TrackBack URL for this entry:
http://betav.com/blogadmin/mt-tb.cgi/1957

Comments

Hey Bill,

You got a new HHG coming out? That's great news. Is that on the US streets in November? or all streets in November? Do you have an ISBN number so I can irritate my book supplier by asking for another book they know nothing about?

Does the code above depend on the SQLBrowser service running?

Also, can't get www.hitchhikersguides.net to work.

Thanks

G.

I fixed the link... sorry, but thanks for pointing that out.
Ah yes, I expect that the SqlBrowser has to be running to get the SS 2005 instances to be visible, but I also expect this is not required for the SS 2000 instances.

Post a comment

(If you haven't left a comment here before, you may need to be approved by the site owner before your comment will appear. Until then, it won't appear on the entry. Thanks for waiting.)