RSS 2.0 Feed
RSS 2.0


Atom 1.0 Feed
Atom 1.0

  Quick tip to discover SQL Servers 


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




                   



Leave a comment below.

Comments

  1. fred 1/30/2004 1:53 AM
    Gravatar
    warning, this command doesn't discover all the servers of a domain. i had tested on a very big network and the discovered server list doesn't contain all the server that i can manager via the manager.

    frederic
    frederic.eveilleau@total.com
  2. fred 1/30/2004 2:15 AM
    Gravatar
    I just want to add a comment on my comment !
    To have the entire list of servers, run the command isql -L on a sql server not via sql server client

    frederic
  3. Ryan Farley 1/30/2004 7:11 AM
    Gravatar
    fred,

    I've noticed the same results in the past even when using NetServerEnum API. Pretty weird. There are some SQL servers in my domain that just don't get discovered.

    Anyway, thanks.
  4. Fridthjof-G Eriksen 6/4/2004 2:19 AM
    Gravatar
    One of the reasons you might not get all servers listed might be that the sel has been configured to not broadcast its presence (security, and recommended pratice), see BOL and "revealing server on network".

    another reason might be that they are on different segments, and name resolution between the two segments are not setup.

    Again, just because you cant find them on the network, apart from a simple ping, does not mean you cant access them in EM or other client tools :-)

    just my 2c..

    Fridthjof
  5. bill 3/13/2008 5:42 AM
    Gravatar
    Just a note if the SQL browser is not running you will not find the server in the list
  6. DC 8/15/2008 10:21 AM
    Gravatar
    Just wanted to say after looking through 100 suggestions its as simple as bill was saying, I didn't have SQL Server Browser Service running. Start this service and now it works!
  7. 1/21/2009 10:53 PM
    Gravatar
    List of SQL Servers in a Domain | keyongtech
  8. thaar 2/24/2009 1:37 AM
    Gravatar
    Dear all,
    I have the following questions:
    1. How many SQL server 2008 and Express can be installed per domain? can you refer me to any MS documentation regarding this. I found a limitation for number of instance per server.
    2. I have a Domain with one sql server installed on W2K3 SP2, with many sql server and express installed on XP SP2, the overall number is 40, all are 2008 entriprise RC0 (all are using single instance per device, some of them are using default instance name and other are using named instance named). My
    problem is that, the instance name can some time note appear in the SQL server management studio and some time is appeared. I checked the browser services, all are working well with domain administrator account. The TCP,named pipe,shared memory all are active and working. My network speed is Gigabit. All machines are powerful. Broadcast and UDP are enabled on CISCO L3 switches.I think the problem is with the number of server/express per domain.

    best regards and thanks

    eng thaar
  9. Praveen 1/17/2010 3:04 AM
    Gravatar
    This is also one of the nice and quick tip to find the servers and if no server is in the list of servers then it's the time to read this post.
    http://praveenbattula.blogspot.com/2010/01/sql-express-instance-is-not-in-list-of.html
Comments have been closed on this topic.



 

News


Also see my CRM Developer blog

Connect:              


Sponsor

Sections