List Available SQL Servers (Windows NT/2000 only)

Category:

Database

Type:

Snippets

Difficulty:

Intermediate

Author:

Intelligent Solutions Inc.

Version Compatibility: Visual Basic 6

More information:

This example uses SQLDMO to return a string array containing all SQL Servers that are visible to the machine. A reference the the Microsoft SQLDMO library is required. The function works only under Windows NT and 2000 and (because it returns an array) VB 6 or above. However, it is easily modifiable to work under VB 5.

Instructions: Copy the declarations and code below and paste directly into your VB project.

Declarations:
' (None)

Code:

Public Function AvailableSQLServers() As String()
'***********************************************
'PURPOSE:       Returns array list name of all SQL Servers
'               on the network that are visible to the
'               machine
'
'RETURNS:       String array containing names of all
'               available SQL Servers (or an array with one
'               element containing empty string if no
'               SQL Servers are available/visible)
               
'REQUIRES:      Reference to Microsoft SQLDMO object library
'               VB6 Because array is returned
'               Assumes Option Base is not set to 1
'               If you don't have VB6, and/or Option Base 1
'               is set, it should not be very hard to modify
'               this code for you own purposes

'EXAMPLE:
'Dim sServers() As String
'Dim iCtr As Integer

'sServers = AvailableSQLServers
'If sServers(0) = "" Then
'    MsgBox "No SQL Servers Available"
'Else
'    For iCtr = 0 To UBound(sServers)
'       Debug.Print sServers(iCtr)
'   Next
'End If
'***********************************************
Dim oServer As New SQLDMO.Application
Dim oNameList As SQLDMO.NameList
Dim iElement As Integer
Dim sAns() As String
Dim lCtr As Long, lCount As Long

On Error GoTo ErrorHandler

ReDim sAns(0) As String
Set oNameList = oServer.ListAvailableSQLServers

With oNameList
    lCount = .Count
    If lCount > 0 Then
        For lCtr = 1 To .Count
            iElement = IIf(sAns(0) = "", 0, UBound(sAns) + 1)
            ReDim Preserve sAns(iElement) As String
            sAns(iElement) = oNameList.Item(lCtr)
        Next
    End If
End With

AvailableSQLServers = sAns
Exit Function

ErrorHandler:
'Return array with one empty element on error
ReDim sAns(0) As String
AvailableSQLServers = sAns
End Function