Listing all servers

Let's say you are designing an application that allows users to connect to a SQL Server and then work off it. Now, to allow users to connect to a SQL Server, it would be nice if the user is presented with a list of all SQL Servers in the network. One simple way to do it is to use SQL-DMO from your application and make a call to its inbuilt function that returns all the SQL Servers listed in the network. Here is the code for the same (using VB6).


Dim oApplication As SQLDMO.Application
Dim oList As SQLDMO.NameList
Dim nCount As Integer
Dim strValue As String

Set oApplication = New SQLDMO.Application
Set oList = oApplication.ListAvailableSQLServers
For nCount = 1 To oList.Count
	strValue = strValue & oList.Item(nCount) & vbCrLf
Next
Set oList = Nothing
Set oApplication = Nothing

MsgBox strValue

The above program lists all the servers in the network. But that's a lot of code to get this done and most important of all, the client application has to have a reference to the SQL-DMO library, which is an extra piece of overhead. So, would it not be nice if by some means we communicate with SQL Server (since all applications will talk to a database) and it returns the list of servers on the network. Well, here is one method to do something similar.


IF (OBJECT_ID ('dbo.listNetworkServers') IS NOT NULL)
	DROP PROCEDURE dbo.listNetworkServers
GO
CREATE PROCEDURE dbo.listNetworkServers AS
BEGIN
	CREATE TABLE #serverList ([output] VARCHAR(200))
	INSERT INTO #serverList EXEC master..xp_cmdshell 'osql /L'
	SELECT * FROM #serverList WHERE [output] IS NOT NULL

	DROP TABLE #serverList
END

Ok. The code is quite simple. We just spool the results of a command into a temporary table and then select from that table. The crux of the stored procedure is the command OSQL /L. OSQL is a command line utility that comes with SQL Server and is used to connect to SQL Server and execute commands from a command line. The /L option tells OSQL to list all the servers. Pretty simple to get the job done :-)

Home