How can I list all available SQL Servers in my application?

A. Two methods :-

1. The ISQL and OSQL commands have a -L option to list servers. This can then be run from xp_cmdshell.

Exec master..xp_cmdshell 'ISQL -L'
Exec master..xp_cmdshell 'OSQL -L'

2. Use SQL-DMO. The method/api is ListAvailableSQLServers. This returns a NameList object enumerating network-visible SQL Servers.

Details on DMO are provided in the Books-Online and/or a separate help file. These are installed as part of any SQL Server install.

A detailed example is below :-

To enumerate all network-visible SQL servers using SQL-DMO objects, create a new standard EXE project and add a reference to sqldmo.rll. This file can be found in \Binn\Resources\1033\sqldmo.rll under the SqlServer70

Now add this code and declaration in your form's code:

Private Function GetAllSqlServerCollection(colSqlServers As Collection)
Dim intIndex As Integer
Dim oApplication As SQLDMO.Application
Dim oNameList As SQLDMO.NameList

Set oApplication = New Application
With oApplication
Set oNameList = .ListAvailableSQLServers
With oNameList
For intIndex = 1 To .Count
colSqlServers.Add (oNameList.Item(intIndex))
End With
End With
Set oApplication = Nothing
GetAllSqlServerCollection = True
End Function

This code quickly fetches a list of SQL servers and can be put inside a combo box's drop-down event to always get a refreshed list of SQL servers on your form.

Hide comments


  • Allowed HTML tags: <em> <strong> <blockquote> <br> <p>

Plain text

  • No HTML tags allowed.
  • Web page addresses and e-mail addresses turn into links automatically.
  • Lines and paragraphs break automatically.