I will start my blogging with Digicorp with very simple but useful trick. When working with multiple project, it is quite common to forget the name of the SQL Server instances. We can retrieve information of all the SQL Server instances running in local network with one single command.
First go to Start >> Run >> CMD (Open command prompt). Once in command prompt run following command based on SQL Server version installed on local machine.
For SQL Server 2000:
C:\> isql -L
For SQL Server 2005 / SQL Server 2008:
C:\> osql -L
OR
C:\> sqlcmd -L
It is also possible that list of SQL Server instances is very long and it needs to be pushed to an output file. In that case add additional command to send output to a file.
For SQL Server 2000:
C:\> isql -L > c:\outputfile.txt
For SQL Server 2005 / SQL Server 2008:
C:\> osql -L c:\outputfile.txt
OR
C:\> sqlcmd -L c:\outputfile.txt
This feature is also important for network security. There are chances that system administrator does not know about some of the SQL Server Instance being active. In large organization this can be huge security threat. If any SQL Server instance is not used it should be turned off because it uses resources of server and adversely affect performance.
Just try this simple command on your local machine and I am sure you will in for surprise. There will be lot more SQL Server Instance running then you might have thought of. Your feedback is very valuable.
Regards,
Pinal Dave
on Windows 2000 isql -L does not give name of instance but just prints hostname
C:\PROGRA~1\Microsoft SQL Server\80\Tools\Binn>isql -L
Locally configured servers:
— NONE –
Announced network servers:
C1059W2K-01
but I have configured my instance as C1059W2K-01\foo
Do you know how to get name of instance on SQL 2000? osql does give instance name on Sql 2005
Santosh,
At present I do not have SQL Server 2000 to test but I have SQL Server 2005 and SQL Server 2008.
I will install SQL Server 2000 and will let you know my findings.
Regards,
Pinal
Pinal,
Any luck? I tried the above, and as said earlier, i end up getting a list of all SQL Server Hostnames in my domain.
Does anyone know if there is another language or set of commands beside SQL for talking with databases?
I’m working on a project and am doing some research thanks
ony works if the sqlbrowser service is running and won’t work across subnets unless your forwarding the packets, right
Pinal wrote articles just for the sake of gaining numbers. Wow…and excuse is he did n’t have 2000 installed. How did he write about isql w/o even check. He is just copying stuff and pasting it in his blog. Come on Panel…I found tons of mistakes in your blog.
Jasa Setting Mikrotik dan Proxy Cepat