Get a list of available servers in a group of SQL servers

Question

How can I retrieve a list of available SQL servers in a group of SQL servers? I plan to put this list in a combo box in VB.NET.

Answer 1

The only way I knew this was to use the command line:

osql -L

But I found the following article that seems to solve your specific goal by filling out the combo box:

http://www.sqldbatips.com/showarticle.asp?ID=45

Answer 2

If you don’t want to bind to SQL SMO, as Ben’s article uses , you can do something like this to find all the SQL servers on your network:

Private Sub cmbServer_DropDown(ByVal sender As Object, ByVal e As System.EventArgs) Handles cmbServer.DropDown
    Dim oTable As Data.DataTable
    Dim lstServers As List(Of String)
    Try
        If cmbServer.Items.Count = 0 Then
            System.Windows.Forms.Cursor.Current = System.Windows.Forms.Cursors.WaitCursor
            oTable = System.Data.Sql.SqlDataSourceEnumerator.Instance.GetDataSources

            For Each oRow As DataRow In oTable.Rows
                If oRow("InstanceName").ToString = "" Then
                    cmbServer.Items.Add(oRow("ServerName"))
                Else
                    cmbServer.Items.Add(oRow("ServerName").ToString & "\" & oRow("InstanceName").ToString)
                End If
            Next oRow
        End If
    Catch ex As Exception
        ErrHandler("frmLogin", "cmbServer_DropDown", ex.Source, ex.Message, Ex.InnerException)
    Finally
        System.Windows.Forms.Cursor.Current = System.Windows.Forms.Cursors.Default

        If oTable IsNot Nothing Then
            oTable.Dispose()
        End If
    End Try
End Sub

SqlDataSourceEnumerator , SQL- 2.0.

Answer 3

In C #, I used odbc32.dll calls

For instance:

[DllImport("odbc32.dll", CharSet = CharSet.Ansi)]

private static extern short SQLBrowseConnect(
IntPtr hconn, StringBuilder inString,
short inStringLength, StringBuilder outString, short outStringLength, out short 
outLengthNeeded);

The documentation for this feature is on MSDN.