August 24, 2008 at 2:25 am
I want to know the all the SQL server instances installed on the particular server?
I am able to see the instances through Control panel, looking for some better way of doing this.
Can anyone please help me here?
August 24, 2008 at 5:11 am
Windows Management Interface can be used to query the services , filter for the program to be "sqlservr.exe", and then write the results to a file. Example of the output file showing two SQL Server instances (one is 2000 and the other is 2005)
sSourceServer|sServiceName|sDrive|sPath|FileName|Extension|Version
USASCHISSQL2-1|SQL2000PRD1|C:|\PROGRA~1\MICROS~1\MSSQL$~1\binn\|sqlservr|exe|2000.080.2187.00
USASCHISSQL2-1|SQL2005|C:|\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Binn\|sqlservr|exe|2005.090.3042.00
The WMI script source is:
Const DblQuote= """"
sSourceServer = "USASCHISSQL2-1" REM Windows 2003
Dim objFileSystem, objOutputFile , strOutputFile
strOutputFile = "./" & Split(WScript.ScriptName, ".")(0) & ".txt"
Set objFileSystem = CreateObject("Scripting.fileSystemObject")
Set objOutputFile = objFileSystem.CreateTextFile(strOutputFile, TRUE)
objOutputFile.WriteLine("sSourceServer|sServiceName|sDrive|sPath|FileName|Extension|Version")
set oWMI = GetObject("winmgmts:{impersonationLevel=impersonate}!\\" & sSourceServer & "\root\cimv2")
Set colServices = oWMI.ExecQuery("Select Name , PathName from Win32_Service")
For Each oService in colServices
IFInStr(oService.PathName,"sqlservr.exe") > 0 then
sServiceName = oService.Name
IF InStr(sServiceName,"$") = 0 then sServiceName = "default" else sServiceName = MID( sServiceName , InStr(sServiceName,"$") + 1 ) end if
sPathName= oService.PathName
IF LEFT(sPathName , 1 ) = DblQuote Then sPathName= MID( sPathName , 2 )
sDrive = MID( sPathName , 1 , 2 )
sPath = MID( sPathName , 3 , InStr( 1 , sPathName , "sqlservr.exe" ) - 3 )
sWQL = "Select FileName,Extension,Version from CIM_Datafile" _
& " Where Drive = '" & sDrive & "'" _
& " and Path = '" & Replace( sPath ,"\","\\") & "'" _
& " and FileName = 'sqlservr' and Extension = 'exe'"
Set oSQLFile = oWMI.ExecQuery ( sWQL )
For Each oFile in oSQLFile
objOutputFile.WriteLine(sSourceServer & "|" _
& sServiceName & "|" _
& sDrive & "|" _
& sPath & "|" _
& oFile.FileName& "|" _
& oFile.Extension & "|" _
& oFile.Version)
Next
end if
Next
SQL = Scarcely Qualifies as a Language
August 24, 2008 at 8:58 am
sqlcmd -L
August 24, 2008 at 10:25 am
Steve Jones advised to use "sqlcmd -L" but there are are a number of problems with this solution:
sqlcmd is for SQL Server 2005 and higher and does not exists for SQL Server 2000, so isql.exe needs to be used.
The "-L" option has a number of problems
1) It will only detect running instances, so if a SQL Server is down, it will not be reported.
2) SQL Server does have an option to not had respond to these broadcast requests, so they would not be reported.
3) When SQL Server is running on a cluster, the name returned will the node name, not the SQL Server name.
4) When SQL Server is running on a cluster and you are using a VPN and the SQL Server 2000 is behind a firewall, it will not be reported, nor will you be able to connect. The bug applies to 2000 only and has been fixed in 2005 where the IP of the cluster node is returned instead of the IP of the SQL Server resource.
SQL = Scarcely Qualifies as a Language
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply