get list of available servers in SQL server group

Question

How can i extract the list of available SQL servers in an SQL server group? Im planning to put that list in a combobox in vb.net. Thank you.

Answer 1

The only way I knew to do it was using the command line:

osql -L

But I found the below article which seems to solve your specific goal filling a combobox:

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

Answer 2

In C# I've used calls to odbc32.dll

for example:

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

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

documentation for that function is on MSDN http://msdn.microsoft.com/en-us/library/ms130926.aspx

Answer 3

If you didn't want to be tied to SQL SMO, which is what Ben's article uses, you can do something like this to discover all 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

The SqlDataSourceEnumerator class is nice because it gives you SQL server discovery right out of the 2.0 framework.

Answer 4

Wow. . . Chris Its so simple and nice to understand. . . excellent work dear. . . thx for sharing this code.