List All SQL Servers using SQLDMO
When I was still in college, one of my professors (Oracle) in PUP and one of the Systems Analyst of PUPILS asked to list all SQL Servers within LAN as part of their functionality in a system, to manually setup the server and save the settings in an INI file. He then introduced me to the SQLDMO. We're still using VB6.0 then.
The last time I used this library is when I created a DTS job scheduler for the eTouch V2 when I was still working with Soluziona Philippines. That time, it was done and used in ASP .NET.
This is also to answer sir cvega's posted comment to Keith's Article regarding how to programatically list all sql servers in your network. Please see the code below...
C#
private void btnRetrieve_Click(object sender, System.EventArgs e)
{
SQLDMO.NameList sqlList = null;
SQLDMO.Application sqlApp = null;
lstServers.Items.Clear();
try
{
sqlApp = new SQLDMO.Application();
sqlList = sqlApp.ListAvailableSQLServers();
for(int ctr = 1; ctr <= sqlList.Count; ctr++)
{
lstServers.Items.Add(sqlList.Item(ctr));
}
}
catch(Exception ex)
{
MessageBox.Show(ex.Message);
}
finally
{
if(sqlList != null)
sqlList = null;
if(sqlApp != null)
sqlApp = null;
}
}
VB .NET (Based using code formatter)
Private Sub btnRetrieve_Click(ByVal sender As Object, ByVal e As System.EventArgs)
Dim sqlList As SQLDMO.NameList = Nothing
Dim sqlApp As SQLDMO.Application = Nothing
lstServers.Items.Clear()
Try
sqlApp = New SQLDMO.Application
sqlList = sqlApp.ListAvailableSQLServers
Dim ctr As Integer = 1
While ctr <= sqlList.Count
lstServers.Items.Add(sqlList.Item(ctr))
System.Math.Min(System.Threading.Interlocked.Increment(ctr), ctr - 1)
End While
Catch ex As Exception
MessageBox.Show(ex.Message)
Finally
If Not (sqlList Is Nothing) Then
sqlList = Nothing
End If
If Not (sqlApp Is Nothing) Then
sqlApp = Nothing
End If
End Try
End Sub
Attached also is the working sample done using C#. Notes though, SQLDMO should be referenced and the DLL can be found in C:\Program Files\Microsoft SQL Server\80\Tools\Binn.