Presents your 
SQL SERVER E-NEWSLETTER for December 19, 2002

<------------------------------------------->

LIST AVAILABLE SQL SERVERS USING OSQL UTILITY

One method for getting a list of SQL Servers is through the SQL utility 
osql. It uses the Open Database Connectivity (ODBC) API rather than the
DB-Library that the previous utility used. The osql utility, when passed
the argument -L or /L, will list the SQL Servers available on the
network. This argument can't be combined with other arguments for the 
utility. If other arguments are present, an error will result.

If you don't want SQL Server to be announced on the network, you can go
to SQL Server 2000 Books Online to the topic "Revealing SQL Server on a
Network." This will give you information on how to hide SQL Server on 
the network.
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

The following is an extremely simple script that creates and executes a
procedure demonstrating the creation of a list of servers that can be
seen from the network using the osql utility:

IF EXISTS(SELECT * FROM sysobjects
WHERE name = 'ListSQLServers' AND type = 'P')
BEGIN
     DROP PROCEDURE ListSQLServers
END
GO

CREATE PROCEDURE ListSQLServers
AS
BEGIN
     SET NOCOUNT ON

     CREATE TABLE #temp
     (ServerName  VARCHAR(255) NULL)

     INSERT #temp
     exec master.dbo.xp_cmdshell 'OSQL -L '

     DELETE #temp WHERE ServerName
          IN ('NULL','Servers:','    (local)') OR ServerName IS NULL

     UPDATE #temp SET ServerName = REPLACE(ServerName, ' ', '')

     SELECT * from #temp

END
GO

EXEC ListSQLServers
GO
DROP PROCEDURE ListSQLServers
GO

----------------------------------------