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
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
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.
I got no active server by “sqlcmd -L” now what should i have to do..?
please help..