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 (https://blog.sqlauthority.com)
87 Comments. Leave new
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
Thank you pinal
Thank you HG Humphrey
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:
Check out the blog, download the free tool and look at the difference. This tool was a lifeline for me.
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.
Very nice. You are one God!
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
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
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 !?
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 ??????
Here’s another way to get SQL Server errors: use a forward slash instead of a backslash in the path e.g.
SQL/SQLEXPRESS rather than SQLSQLEXPRESS
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
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
Hi Pinal,
How to display all SSIS in the network.
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.
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
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.
Dear Pinal,
Does this work for SQL server 2005 too?
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
sir! i hve typed osql -L in command prompt…but i got..
this msg..
Servers:
—NONE—
please reply me….coz i got same error i.e..
error 40-could not open a connection to SQL Server
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.
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!