Shuja Ali
Mar 8th, 2006, 05:54 AM
Sometimes we need a list of all the SQL Servers present on the network. There are lot of code snippets on the internet to do just that, however I did not find anything in the VBF Codebank.
So here is a function that will return an array f all the SQL Servers present in the network. In order to run this function you will have to set reference to Microsoft SQLDMO Object Library (Goto Projects-->References and select Microsoft SQLDMO Object Library)
'Function : listSQLServers
'Input Parameter : None
'Return Value: Returns an Array that consists of the
' names of the SQLServers available
Public Function listSQLServers() As String()
'set reference to Microsoft SQLDMO Object Library
'prior to using this code
Dim sqlApp As New SQLDMO.Application
Dim serverNameList As SQLDMO.NameList
'an empty array that will hold all the servernames
Dim serverNames() As String
Dim loopCounter As Long
'get all the SQL Servers available
Set serverNameList = sqlApp.ListAvailableSQLServers
'if no servers exist then return to the calling function
If serverNameList.Count = 0 Then
Set sqlApp = Nothing
Set serverNameList = Nothing
Exit Function
End If
'Redim the array with the proper count
ReDim serverNames(serverNameList.Count)
'loop through all the servers
For loopCounter = 0 To serverNameList.Count
'add to the array
serverNames(loopCounter) = serverNameList.Item(loopCounter)
Next
listSQLServers = serverNames
'release objects
Set sqlApp = Nothing
Set serverNameList = Nothing
Exit Function
End Function
So here is a function that will return an array f all the SQL Servers present in the network. In order to run this function you will have to set reference to Microsoft SQLDMO Object Library (Goto Projects-->References and select Microsoft SQLDMO Object Library)
'Function : listSQLServers
'Input Parameter : None
'Return Value: Returns an Array that consists of the
' names of the SQLServers available
Public Function listSQLServers() As String()
'set reference to Microsoft SQLDMO Object Library
'prior to using this code
Dim sqlApp As New SQLDMO.Application
Dim serverNameList As SQLDMO.NameList
'an empty array that will hold all the servernames
Dim serverNames() As String
Dim loopCounter As Long
'get all the SQL Servers available
Set serverNameList = sqlApp.ListAvailableSQLServers
'if no servers exist then return to the calling function
If serverNameList.Count = 0 Then
Set sqlApp = Nothing
Set serverNameList = Nothing
Exit Function
End If
'Redim the array with the proper count
ReDim serverNames(serverNameList.Count)
'loop through all the servers
For loopCounter = 0 To serverNameList.Count
'add to the array
serverNames(loopCounter) = serverNameList.Item(loopCounter)
Next
listSQLServers = serverNames
'release objects
Set sqlApp = Nothing
Set serverNameList = Nothing
Exit Function
End Function