I recently came accross something cool (albeit small) for a quick way to discover SQL Servers on your local network. I'm not talking about a programatic way to do so (that's that NetServerEnum API and DMO are for), but more of just a manual way without needing to discover them through SQL Enterprise Manager etc.

You can simply use the installed 'osql' utility at the command-prompt. 'osql' is a command-line utility installed by SQL Server (and MSDE). You can use it to connect to a SQL server and run queries, etc. Basically it is a command-line version of Query Analyzer (or isql/w as it was called back in the day of SQL 6.5 and earlier). Bring up a DOS prompt and type the following:

isql -L

It does need to be an upper-case 'L' as the lower-case 'l' is the parameter to set the login timeout. But when you run this you'll get a list of all SQL servers on the network (including the configured servers such as the ones you have created alias for in the SQL Client Network Utility). On my network I get the following:

C:\>osql -L

Servers:
    CFXDEV02
    CFXPORT01
    MORPHEUS
    MORPHEUS\SQL2000
    SUPERFREAK1
    SUPERFREAK1\SQL2000

A few things to point out. First of all, it also returns named instances found. Something you don't get using the NetServerEnum API (although you do get that via DMO). That is cool. Also, in this list, the servers CFXDEV02 and CFXPORT01 are configured servers I access via a VPN connection, they are not located on my network although they appear in the list because I have created aliases for them locally. But note that there is no distinction made bewteen the local servers and the configured ones.

So I was curious now. I decided to see if the results varied using the older 'isql' command-line utility. As an aside, you should always use osql over isql. The osql utility is a replacement for isql. The isql utility is built on the older DB-Library API (which was from the SQL 6.5 and earlier days). It is out-dated. Although both osql and isql are installed with SQL Server 2000, The DB-Library API, which isql uses, remains at a SQL Server 6.5 level. This means that you won't be able to use new SQL features via isql such as access columns defined with the ntext data type and truncates any char, varchar, nchar, or nvarchar columns longer than 255 bytes. It also cannot retrieve results as XML documents. Anyway, let's move on.

Using the same command-line parameter with isql does produce some slightly different results. Here's what I get on my own network:

C:\>isql -L

Locally configured servers:
    CFXPORT01
    CFXDEV02

Announced network servers:
    MORPHEUS
    SUPERFREAK1

You'll notice two things right away. isql does make the distinction between local servers on my network and the configured ones. But it also does not list the named instances.

If you need to save this list to a file, you could always just redirect:

C:\>osql -L >> MySqlServerList.txt