SQL SERVER – Find All Servers From Local Network – Using sqlcmd – Detect Installed SQL Server on Network

I recently had requirement to create list of all the SQL Server on local network. I remembered that I had written similar script a year ago SQL SERVER – Script to Find SQL Server on Network. When I looked at it, I realize that I had written it for SQL Server 2000 and used “isql” utility, which is deprecated now. I quickly wrote down updated script using “sqlcmd”. Command “osql” still works in SQL Server 2008.

Go to command prompt and type in “osql -L” or “sqlcmd -L”.

Note one change between osql and sqlcmd is that osql has additional server “(local)” listed in the servers list which is in fact same as “SQL” in my case. While “sqlcmd” gives accurate result.

Reference : Pinal Dave (http://blog.SQLAuthority.com)

74 thoughts on “SQL SERVER – Find All Servers From Local Network – Using sqlcmd – Detect Installed SQL Server on Network

  1. If you have a large number of SQL servers in your network, you can push the results of SQLCMD -L to an output file as DOS doesn’t allow you to scroll-up past a certain point. To do this you would simply type at the command prompt:

    SQLCMD -L > c:\servers_filename.txt

    Like

  2. Hi Pinal –

    Thanks for the entry. I have noticed that sometimes searching with this method, I have missed quite a few of the named instances and clusters in my network.

    There is a great free tool from Microsoft called the Microsoft Assessment and Planning tool. This tool is great for a lot of options (It dumps into a nice Excel file) and it found ALL of my instances.

    I blogged about that tool here: http://www.straightpathsql.com/blog/2009/2/25/what-sql-instances-are-installed-on-my-network.html

    Check out the blog, download the free tool and look at the difference. This tool was a lifeline for me.

    Like

    • I know this is an old post but I just tried MAP (Oct 2014). It’s a great tool that returns a whole lot of useful information as a multi-tab spreadsheet. Just be advised that much like SQLCMD -L, it misses servers, as well. It’s also a shame there’s no apparent option to write the data it collects to a table in SQL Server.

      Like

  3. it sounds silly,not being able to understande all this technicall mushup, but since I am so troubled banging my head against this SqlServe wall , whoud kindly request You
    to advise me .
    I’am trying lagely to attach aspnetdb to my db to have it all
    and to atach AspNet_CacheTablesForChangeNotifications.
    starting with >sqlcmd -E -S .\SqlExpress –than
    >sp_attach_db “aspnetdb” , C:\Program Files\….
    >go
    >aspnet_regsql.exe -E -S .\SqlExpress -d aspnetdb -ed

    I get that I have incorect syntax near E , if remove id , near S etc.
    I reinstalled SqlExpress , even VS 2005 ant still stuk.
    Started learning over sqlcmd utility but nothing found that speaks of it …
    If it doesn’t boders you , would you be so kind to help me out , or just to give me hint….
    in advance thanks

    Like

  4. me again , sory for interupting but need to brag, I’ve solved my problem though have some problems with my instalations of Sql server 05/express but any how ,
    : ) I smile again , cool and thanks

    Like

  5. Dear Pinal, what if , when on my vs2005 cmd I set..
    sqlcmd -L and get no server listed…
    what would that now could be…
    this errMessage have I got trying to attach DB
    HResult 0xFFFFFFFF, Level 16 State 1
    Than I refered to your blog , among others…
    Still working on it, can’t get any connection beetwen my vs2005 and MSSQLSMSE !?

    Like

  6. Pingback: SQL SERVER – FIX : ERROR : (provider: Named Pipes Provider, error: 40 – Could not open a connection to SQL Server) (Microsoft SQL Server, Error: ) Journey to SQL Authority with Pinal Dave

  7. I need help

    I need to install automatically sql server 2005 express and attach a database to it automatically with a username and a password any help please , this is a desktop application and not a web one

    any help ??????

    Like

  8. Hello George,

    You can perform automatic installation of MS SQL Server using setup.exe command line utility.

    For installation of MS SQL Server specify following parameter:

    Setup.exe /q /ACTION=Install /FEATURES=SQL /INSTANCENAME=MSSQLSERVER /SQLSVCACCOUNT=”” /SQLSVCPASSWORD=”” /SQLSYSADMINACCOUNTS=”” /AGTSVCACCOUNT=”NT AUTHORITY\Network Service”

    It is also explained in following blog:

    http://blog.sqlauthority.com/2007/04/10/sql-server-2005-silent-installation-unattended-installation/

    Regards,
    Pinal Dave

    Like

  9. Hi,
    Sir i m new to sql server and i find ur all aticles very interesting and helpful.
    i m working with sqlexpress2005 with visual studio 2008 with i find this error while run my program on IIS. Plz reply or write article on this issue
    “CREATE DATABASE permission denied in database ‘master’.”
    thnx in advance

    Like

  10. Hello Haleema,

    The error is clear and complete in itself that the user that is running the script does not have CREATE DATABASE permission. If you can allow that user to create database than grant the permission and error would not occure.

    Regards,
    Pinal Dave

    Like

  11. I cant get to connect to SQL Server Express 2008 via ODBS sources. I get various errors

    trying the ideas here

    osql -L reports 2 connections

    . (just a dot)
    e-machines\sqlexpress

    sqlcmd -L reports nothing

    any ideas.

    Like

  12. dear sir
    i have a problem when i connected to bsnl wirelessland line internet service hardly able to connected 20.after that i dissconnected my inernet showing these messages .problem loading page sometime servernot found and the connection has timeout etc.i am using windows xp service pack 3.os.kindly solve my problem

    Like

  13. Hi. Great tool, however, how do you get a list of all the sql servers across a WAN?, as ours are spread allover.
    Regards
    Andy.

    Like

  14. Funmilola, i have just tested it on my pc on a larrrrge LAN, and running it from SQL2008 i was able to pick up 2005 and 2000 databases.

    I’m intruiged as it also picked up some pc’s identified as SQL 2008 Management tools – i’m aware it installs the db on there too – but it did not pick all of them up. Saying that – i got what i needed :)

    Thank you Pinal

    Like

  15. have you tried sqlcmd -L on dos prompt.
    if you want to take output in text file then
    sqlcmd -L >c:\serverlist.txt
    and once your dos command complete, you will see this file in C drive and can see your server list in the network.

    Like

  16. Hi.

    I’ve been trying to invoke a sqlcmd statement from within a script as I’ve got multiple scripts to run for various clients. I have not been able to figure out what the syntax error I’m having that makes it unable to find the script file on my local computer. Here is a sample of my statement:

    EXEC master.dbo.xp_cmdshell ‘sqlcmd -S db-server -U dba -P sql -i “C:\Projects\Create_Test_DB.sql”‘

    If I change this to

    EXEC master.dbo.xp_cmdshell ‘sqlcmd -S db-server -U dba -P sql -i “\\server-name\Projects\Create_Test_DB.sql”‘

    where the script is copied into the db server “server-name\Projects” directory, the statement will run.

    Please help!

    Like

  17. hey Pinal,

    Am trying to see all the databases on a local network. Both that send instance or does not send instance? Can u help me?
    thanks in advance

    Like

  18. Hi Pinal,

    I am trying to connect one of the remote database from my .Net application, but i am not able to connect as i am getting the following error.
    ——————————-
    A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessbile. Verify that the instance name is correct and that the SQL Server is configured to allow remote connections. (provided: Named Pipes Provider, error: 40- Could not open a connection to the SQL Server)
    ——————————-

    I am having both SQL Server 2005/2008 as well on my system.

    I would be really grateful if you can help me on this.
    Thanks in advance.

    Rgds
    Prakash

    Like

  19. I installed SQL Express 2005 on my laptop, I want to use as my database engine for a small business. I have this error (53)…,and SQL browser is stopped, each time I start it, it will give error…what can i do…?

    Like

  20. can share your net thou SQL Server 2005/2008 with out my say so .. or can some 1 hack ur network thou cable network .. like know how stop People gain access too your pc .. stop how stop People .. used my net though wireless connection’s

    i ve unstalled SQL Server 2005 from my pc . becase think some try used get accesss too my pc .

    i need simlpe way’s understanding what is SQL Server 2005 act do ..i am not pc tech .. please some 1 till what do here protect my salf some time do think fire wall do it’s job
    hop some can help me this isssue be great ta ..

    from proline .

    Like

  21. dear sir, i executed the command sqlcmd -L in the prompt and i found the output as :

    A-9
    A-9\NEW
    A-9\SQLEXPRESS

    But i have created an instance MSSQLSERVER with windows authentication but this is not shown in the above output, also in visual studio when i want to connect to this instance through data source MS Sql Server then it does not shows this instance name in the list

    Like

  22. sir can u tel me how to set the following in object explorer ( ms sql 2005)

    server type:

    server name:

    authentication:

    login name:

    password:

    plz explain how to set these………..

    Like

  23. Hello Pinal,

    Is there any way that I can get all the servers list in our network with version of sql server?
    This is really important for us to know, how many servers are in our network with version.

    Like

  24. hi, i run the command….but i found that i don’t have one in the list. my sever is the domain controller. and i can not access to it. can i add the local server to the list?
    thank you

    Like

  25. Hello Sir,

    i am having problem ,
    I can’t access my Network SQL Instance ,
    i enabled SQL SERVER BROWSER ,
    i can access my Instance on same system , am using LAN and trying to search for instance so it is not showing……

    PlzZz help…..me

    ThanxXx in Advance

    Like

  26. Hello,
    Thank you for every body:)
    i have problme that i do all command you presented such as
    sqlcmd -L and osql -L

    but i don’t see and servers.
    also when i go to sql server managment stiduo
    i can’t login because there is no server
    note i have installed
    sql server 2008 SQLManagementStudio_x86_ENU.

    please could you check my status
    Regards,

    Like

  27. Hi Sir,

    Please help me. I can’t attach MS SQL to my Visual Studio 2005/2008/2010. The Visual Basic can’t find the SQL Database. either I can’t find the SQL database to my savepoint even thoe my Laptop is newly reformatted.. help me please, asoon as posible..

    God bless..
    Mr.JC

    Like

  28. We have few servers in remote location and couple of in house servers
    everything is in same domain but when i run SQLCMD -L command on server at remote location i see only servers of remote location,,,, and when i run the same command on any of the inhouse servers it gives list of all inhouse servers, why is it so despite i have everything in same domain

    Like

  29. You had mentioned to, “Run “sqlcmd -L” in your command prompt to ascertain if your server is included in your network list.” My server is not in that list, so how do I add it to it? I am using Windows Server 2003.

    Thanks BC

    Like

  30. I am a student and i want to use SQL SERVER 2005 for basic use and when i will opened SQL SERVER STUDIO 2005 its ask server name but i have not any server then how can i use it ?

    Like

  31. Sir when i give this in cmd it displays

    C:\Users\poorni>sqlcmd -L

    Servers:

    C:\Users\poorni>osql -L

    Servers:
    — NONE —

    what to do for this… sir please pl pl help me i am breaking my head over this and the deadline is near forward….

    Like

  32. An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: Named Pipes Provider, error: 40 – Could not open a connection to SQL Server)
    After facing this error, when i wrote this
    sqlcmd -L in command prompt there is showing not any server name,
    what should i do now

    Like

  33. Hi Pinal,

    We installed sql server, everything was working fine till someone changed the drive name in the system. Now we are unable to login to the sql server. Is there any way to sort out this issue.

    Thanks

    Manju

    Like

  34. Hi I followed your way to a tee, all i got was it is not a cmd they know. I am here due to messed up attempt to remove MS syql 2008 server from a used laptop, i am not a programmer. I thought program would be removed via usual uninstall method in control panel under uninstall programs, was i wrong.I see why buddy sold me this used laptop now. Googled many sites to get rid of the server but this poor laptop about to meet a messy end in front of the first microsoft employee i run into.

    Like

  35. Hello Sir!!I have installed sql server 2008 from microsoft site,it x86 ,when i tried to open management studio in windows authentication i am unable to locate my local server,can anyone help me with this to solve the problem.I also tried to check in SQL server configuration manager in that i tried to locate SQL servers services even there also i couldn’t locate any server.

    Like

  36. Hi Melwin (and others), the SQL Browser service must be running on your machine in order for SQLCMD or OSQL to discover SQL instance names.
    Have a look in the SQL Server Configuration Manager: If it is not running, or cannot be started from there, then go to Administrative Tools, Services and enable the service (automatic), and start it.

    Like

  37. Hi Sir,I have Create one module(VB.NET),I need EXE file,In My Application 2 Exe are there,I don’t know Which file is correct one?
    1.Module name-BIN-DEBUG-EXE
    2.Module name-OBJ-DEBUG-EXE

    At the same time need to implement in Another Server(It’s Located other branch of our Organization,They have Diff Server)I changed that server name as destination server name,But It Shows an Error like——-A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: Named Pipes Provider, error: 40 – Could not open a connection to SQL Server),,
    Plz Advice How to solve..

    Like

  38. I was using sqlcmd -L to list my servers but suddenly, it stops displaying the list. When I connect to the other node of my cluster, it works. (instances are balanced at equal side) Do you have any idea for this issue?

    Like

  39. sir! i hve typed osql -L in command prompt…but i got..
    this msg..
    Servers:
    —NONE—

    WHAT DOES TAT MEAN

    i have sqlserver 2005

    Like

  40. Pingback: SQL SERVER – Weekly Series – Memory Lane – #021 | SQL Server Journey with SQL Authority

  41. Hi Pinal,

    I got some issue related this “sqlcmd -L”, can you help me out ?

    currenty I have 3 server, let say server1, server2 and server3, the new server3 just setup few week ago. and my issue is when I remote desktop to server2 and run the cmd : sqlcmd -L, I only can see the server1 and server2, but my server3 I can’t see it there. and in server2 I tried to run \\server3 , it’s work fine and see the sharing folder in server3. do you have any solution for that ? I am desperate about this kind of issue and my boss said this is the sql issue and not the network issue and ask me to solve it. Thank you

    Jimmy

    Like

  42. I am getting following error when my application which is installed at Server which is on domain and db server is on WORKGROUP.
    A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: Named Pipes Provider, error: 40 – Could not open a connection to SQL Server)

    Any solution I have checked with all the suggestions given above, works fine when both machines are on domain.

    Like

  43. TOTALLY frustrated with MS SQL … every were I look they say OSQL -L or sqlcmd -L will give you a list of servers/instances, BUT, I too receive NONE or no list at all… I see lots of folks having this same problem, bot I see no workaround or fixes…

    Like

  44. How do I ensure that My server is not available in a Virtual Server Environment in Network servers’ list? Coz I dont want anybody from outside my org to even connect and try some pwd using default SA login….
    Any help would certainly help us lot here.

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s