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)
Like this:
One blogger likes this post.
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
Nice Suggestion 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: 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.
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 !?
[...] In addition, ensure that your remote server is in the same network. Run “sqlcmd -L” in your command prompt to ascertain if your server is included in your network list. You can even find tutorial for the same here SQL SERVER – Find All Servers From Local Network – Using sqlcmd – Detect Installed…. [...]
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 SQL\SQLEXPRESS
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
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!
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
Thankz a lot..:D
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
Make sure the application is using the correct connectstring and the server is available over network
VERY THANKS
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…?
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 .
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
Can you check if it comes under your system’s network?
When I run the “sqlcmd -L” on the CommandPrompt, it only outputs..
Server :
..and nothing more, what does it mean?
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………..
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.
In the command prompt use, osql -L
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
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
Hey guys, has anyone tried the following SQL command out? It lists servers and let’s you select which one to attach.
DROP *
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,
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
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
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
sqlcmd -L does not give the complete list. However osql -L gives.
Tayfun Demirbilek
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 ?
no server at all
Server :
only
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
have ur problem solved.. i hve the same problem.. what u did for this….
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
thank you pinal sir….
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….