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
)
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 !?
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
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….
same problem i am facing ,,u got ur problems ans ? if s then pl forword me
Thanx
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
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
hiiiii sir this type of out put is not show on my commpand prompt,,,, so what can i dooo????
Display all Server in network i have a query requery….?
Display all Server in local Network in my vb.net program
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.
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.
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.
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..
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?
osql -L
Sir i try it but rerpl
——-none———–
so what i have to
sir! i hve typed osql -L in command prompt…but i got..
this msg..
Servers:
—NONE—
WHAT DOES TAT MEAN
i have sqlserver 2005
Pinal, thank you so much for sharing this! I really needed it!
Appreciate it so much!
Cheryl
Pingback: SQL SERVER – Weekly Series – Memory Lane – #021 | SQL Server Journey with SQL Authority
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
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.