I recently created an installation program that needed a list of SQL Servers. Google found four different methods. Two use SQL Server Management Objects (SMO), one reads the registry and one uses executable SQLCmd. I wrote a VB.Net Windows application to test the methods. The methods are encapsulated in module cGetSQLServers.vb. I added a method which reports the names of the databases attached to a selected server. This becomes the July 2009 tip-of-the-month.
2011/02/09 - Today I converted the application to VS/2008 using SMO version 10. Both versions are still available. See below.
The Demo Solution - Upon initial execution the demo uses the four methods to find SQL Servers available on the host. It takes a few seconds.
Three servers were found. The default server is HP-IQ804.
An 'X' indicates which method found the server.
The remote Dell1705 and Dell8500 were found using methods 10 and 40.
The local HP-IQ804, with instances, was found using all methods.
|10||SmoApplication.EnumAvailableSqlServers||Jasper Smith||Go To|
|20||Registry.LocalMachine||Jasper Smith||Go To|
|Jasper Smith||Go To|
|40||Using SQLCmd||Superna Parajuli||Go To|
Using the Demo
bLocalOnly - When checked only the local host's SQL Servers are reported.
bAllowDuplicates - When checked all SQL Servers found with the selected methods are reported. If not checked then only the first occurrence of the SQL Server is reported.
The Methods - All or none of the methods can be checked. Methods 10 and 40 are very slow because they search the network for servers. Methods 20 and 30 are very fast because they only search the local host but they will not report unregistered servers.
Get Servers - Click this button to report the SQL Servers found using the selected methods.
Test Connection - Click this button to attempt to connect to the selected SQL Server.
Properties - Click this button to display the SQL Server Properties.
Get Databases - Click this button to display a list of databases attached to the selected SQL Server.
The 'Test Connection' button was clicked and reported that a
connection to the selected server was successful.
The 'Properties' button was clicked.
Information about the selected SQL Server's settings is reported.
The 'Get Databases' button was clicked.
A list of databases attached to the selected server is displayed.
A connection to the selected server failed.
Operative Code - Code similar to this is used to call GetServers in class cGetSQLServers and use properties of class cSQLServer:
Dim oGSS As New cGetSQLServers
If .GetServers() Then
For Each o As cSQLServer In .oCollServers
Module cGetServers.vb contains two classes: cGetServers and cSQLServer. cGetServers contains the methods described in this tip. cSQLServer describes the properties of each entry in collection oCollServers.
Class cGetServers Public Methods
Method Operands Sample Comment GetServers If .GetServers Returns True if successful.
Returns a collection of servers in oCollServers.
Format is defined in class cSQLServer
GetDatabases [sServer] If .GetDatabases Returns True if successful.
Returns a collection of database names in oCollDatabaseNames.
Format is string.
Use the default server if sServer is not supplied.
DoConnectSMO [sServer] If .DoConnectSMO Returns True if successful.
Use the default server if sServer is not supplied.
ExistsInCollectionDatabase sDBName, sServerAlreadyAttachedTo Use this method in conjunction with GetServers to determine if a database exists in any of the servers found in oCollServers.
Returns True if found and returns the name of the server in string sServerAlreadyAttachedTo.
This method is not demonstrated here.
Class cGetServers Public Properties
Property - All Boolean Comment bLocalOnly Return only local SQL Servers. Default is False bAllowDuplicates Use all selected methods. Don't stop with the first one found. Default is False. bGetUsing10EnumAvailableSqlServers Use this method if True, default is True bGetUsing20FromRegistry Use this method if True, default is True bGetUsing30FromEnumRegisteredServers Use this method if True, default is True bGetUsing40FromSQLCmd Use this method if True, default is True
Class cSQLServer Public Properties
Property - Use as ReadOnly Sample Comment sName() As String HP-IQ804\SQLEXPRESS The server and instance name sServer() As String HP-IQ804 The server name. sInstance() As String SQLEXPRESS The instance if any. Can be blank.
I have never been able to connect to instance MSSQLSERVER.
Does anyone know why?
sVersion() As String 9.00.1399.06 Only method 10 returns a version number. bLocal() As Boolean True If the local host then True. b10FromEnumAvailableSqlServers() As Boolean True Is True if this method was used to find the server. b20FromRegistry() As Boolean False Is True if this method was used to find the server. b30FromEnumRegisteredServers() As Boolean False Is True if this method was used to find the server. b40FromSQLCmd() As Boolean False Is True if this method was used to find the server.
Other Modules Used in the Demo
|Module - Click link for More Info||Comment|
|modLocationLocationLocation.vb||Used to save and get the location of the forms.
|modRegRead.vb||Read and Write to the registry.
Save selected values between sessions.
Download VB.Net Code
Previous Tip Tip Index Next Tip