AutoDiscovery of SQL Servers using .NET Framework

[mpappert]

Hi All,

 

I was wondering if there is a .NET method for determining all of the SQL Servers on the network, like you can for say local hard disks:

 

Dim drive As String
For Each drive In Environment.GetLogicalDrives()
   Console.WriteLine(drive)
Next

 

Possibly some code like this but that enumerates the SQL Servers on the local network.

 

Thanks!

M.

[mpappert]

I've found this VB6 code that enumerates the list of available servers, I'll try converting it to .NET, but if anyone knows of .NET classes that will do this, please let me know!

 

Thanks!

M.

 

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

[Nerseus]

I believe that the SQLDMO objects have NOT been converted to .NET. You'll have to use the COM version for now.

 

-Nerseus

[mpappert]

The COM version? Can u give me a few more details, which COM object do I reference?

 

Thanks!

M.

[Nerseus]

The code snippet you posted uses COM (it's VB6 sample code, must be COM). The SQLDMO object IS COM. Check out the first line of your code snippet:

Dim oServer As New SQLDMO.Application

That's creating a COM component from the library SQLDMO, the class is called Application.

 

Just add a reference to your .NET project. Click on the COM tab and find the reference or browse to it. Then you can create the SQLDMO.Application object with new() and access all the same methods/properties.

 

-nerseus

[itaLix]

I'm stuck on this...

 

I have included the reference but I keep receiving this error:

 

An unhandled exception of type 'System.InvalidCastException' occurred in WindowsApplication2.exe

 

Additional information: QueryInterface for interface SQLDMO.NameList failed.

 

The code I'm using is this:

 

Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
       Dim i As Integer

       Dim oSQLApp As SQLDMO.Application
       oSQLApp = New SQLDMO.Application()

       Dim oNames As SQLDMO.NameList

       oNames = oSQLApp.ListAvailableSQLServers

       List1.ClearSelected()
       For i = 1 To oNames.Count
           List1.Items.Add(oNames.Item(i))
       Next i
   End Sub

 

I was successful in making this work in vb.6 but what am I missing in the .NET version.

 

Thanks.

[itaLix]

This might be an issue with the .NET 1.0 framework.

 

I'll try to see if 1.1 has resolved this issue.

[Kiril]

This works for me in VB.NET:

 

Dim dmoApp As New SQLDMO.Application

Dim dmoSvrList As SQLDMO.NameList

 

dmoSvrList = dmoApp.ListAvailableSQLServers

 

Dim i As Integer

 

For i = 1 To dmoSvrList.Count

cboServers.Items.Add(dmoSvrList.Item(i).ToString)

Next

[kyma]

You have to install the latest SQL2000 service pack on you client machine (SP3a at present)

 

I know this doesn't seem logical, especially since the same code works in VB6 without the latest SP, but I think it may have to do with the COM Interop libraries used and the .Net framework security model.

 

:cool: