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)

About these ads

73 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

  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.

  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

  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

  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 !?

  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 ??????

  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

  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

  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

  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.

  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

  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.

  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

  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.

  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!

  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

  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

  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…?

  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 .

  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

  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………..

  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.

  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

  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

  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,

  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

  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

  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

  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 ?

  31. I ran the command on the SQL Server Cluster 2008 SP1

    osql -L returns:
    Servers :
    — NONE –

    sqlcmd -L returns
    Servers:

    why at least the local server is not lsited?
    Thanks,
    DOm

  32. Hi pinaldave,
    We need your help.Our network has sql server 2008 R2 installed in each computer.We cannot access each others sql server.We are getting error-26.Any help?Thanks in advance

  33. 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….

  34. 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

  35. 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

  36. 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.

  37. 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.

  38. 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.

  39. 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..

  40. 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?

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

    WHAT DOES TAT MEAN

    i have sqlserver 2005

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

  43. 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

  44. 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.

  45. 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…

  46. 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.

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