nmxsupport
asked on
vbscript extract sql instances and version
Hello using vbscript and WMI or otherwise I am looking to list all SQL instances and their SQL version including build level (e.g. 2005.90.3042). Anyone got suggestion to achieving this?
Thanks
Thanks
Hi, I've never done much scripting with SQL Server, but here's one I used to get instances.
https://www.experts-exchange.com/questions/22925099/VBScript-code-to-list-all-SQL-2000-2005-instances-given-a-server-name.html
Regards,
Rob.
https://www.experts-exchange.com/questions/22925099/VBScript-code-to-list-all-SQL-2000-2005-instances-given-a-server-name.html
Regards,
Rob.
ASKER
Hmmm that works well to give all databases on the default instance - but looking to list all the instances on a named server and the version SQL of each one.
How does this go?
Rob.
Rob.
arrServers = Array(_
"SQLServer1", _
"SQLServer2" _
)
strOutputFile = "Servers_DBs.txt"
Set objServer = CreateObject("SQLDMO.SQLServer2")
'Set Authentication for Server
' Login with current Windows account
objServer.LoginSecure = True
strDBs = ""
For Each strServerName In arrServers
objServer.Connect strServerName
If strDBs = "" Then
strDBs = "Server: " & strServerName & VbCrLf & _
"Major Version: " & objServer.Application.VersionMajor & VbCrLf & _
"Minor Version: " & objserver.Application.VersionMinor & VbCrLf & "========================"
Else
strDBs = strDBs & VbCrLf & VbCrLf & "Server: " & strServerName & VbCrLf & _
"Major Version: " & objServer.Application.VersionMajor & VbCrLf & _
"Minor Version: " & objserver.Application.VersionMinor & VbCrLf & "========================"
End If
MsgBox strDBs
For Each objDatabase In objServer.Databases
strDBs = strDBs & vbCrLf & objDatabase.Name
Next
objServer.Disconnect
Next
Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objOutputFile = objFSO.CreateTextFile(strOutputFile, True)
objOutputFile.Write strDBs
objOutputFile.Close
Set objOutputFile = Nothing
Set objFSO = Nothing
MsgBox "Done. Please see " & strOutputFile
ASKER
Hi Rob, this works to list the sql version of the default instance for a given array of servers. However for each server there can be many other instances - each running their own version of SQL - it was this part I'm trying to extract.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
So from that link I posted, what does this code give you?
If you browse through the keys and values in
HKLM\Software\Microsoft\Mi crosoft SQL Server
on one of your SQL Servers, does that tell you what their versions are? If so, we could pull those out too.
Regards,
Rob.
If you browse through the keys and values in
HKLM\Software\Microsoft\Mi
on one of your SQL Servers, does that tell you what their versions are? If so, we could pull those out too.
Regards,
Rob.
Set objAdRootDSE = GetObject("LDAP://RootDSE")
Set objWMIService = GetObject("winmgmts:\\" & "." & "\root\cimv2")
Set objRS = CreateObject("adodb.recordset")
Const HKEY_LOCAL_MACHINE = &H80000002
varConfigNC = objAdRootDSE.Get("defaultNamingContext")
strConnstring = "Provider=ADsDSOObject"
arrServers = Array(_
"SQLSERVER1", _
"SQLSERVER2" _
)
For Each strServer In arrServers
strWQL = "SELECT * FROM 'LDAP://" & varConfigNC & "' WHERE objectCategory='Computer' and Name='" & strServer & "'"
objRS.Open strWQL, strConnstring
Do until objRS.eof
Set objServer = GetObject(objRS.Fields.Item(0))
strServerName = objServer.CN
Set colItems = objWMIService.ExecQuery("Select * from Win32_PingStatus Where Address = '" & objServer.DNSHostName & "'")
For Each objItem in colItems
If objItem.StatusCode = 0 Then 'The Computer is Pingable
Set objRegistry = GetObject("winmgmts:{impersonationLevel=impersonate}!\\" & strServerName & "\root\default:StdRegProv")
strKeyPath = "SOFTWARE\Microsoft\Microsoft SQL Server"
strValueName = "InstalledInstances"
objRegistry.GetMultiStringValue HKEY_LOCAL_MACHINE,strKeyPath, strValueName,arrValues
If IsNull(arrValues) = 0 Then 'It's a SQL Server! Enumerate it's instances
strMsg = strServerName & " is running the following SQL Instance(s): "
For Each strValue In arrValues
If strValue = "MSSQLSERVER" Then strValue = "Default Instance"
strMsg = strMsg & vbCrLf & strValue
Next
MsgBox strMsg
End If
Set objRegistry = Nothing
End If
Set objServer = Nothing
Next
objRS.movenext
Loop
objRS.close
Next
Set objWMIService = Nothing
Set objRS = Nothing
Set objAdRootDSE = Nothing
Or, as SirBounty provided in his first post (did you try that?), perhaps the command line tools can do this better...
http://www.mssqltips.com/tip.asp?tip=1113
Rob.
http://www.mssqltips.com/tip.asp?tip=1113
Rob.
ASKER
Rob, thanks for the links - they have provided me with a way to move forward. I've used the various sources to put together something which works for me.
Kind Regards.
Kind Regards.
sub chkSQL(strserver) '1.9
ON ERROR RESUME NEXT
Set objRegistry = GetObject("winmgmts:{impersonationLevel=impersonate}!\\" & server & "\root\default:StdRegProv")
strKeyPath = "SOFTWARE\Microsoft\Microsoft SQL Server"
strValueName = "InstalledInstances"
objRegistry.GetMultiStringValue HKEY_LOCAL_MACHINE,strKeyPath, strValueName,arrValues
If not IsNull(arrValues) Then
st = server
For Each strValue In arrValues
if strvalue = "MSSQLSERVER" then
strInstance = server
else
strInstance = server & "\" & strvalue
end if
Set cxn = Wscript.CreateObject("ADODB.Connection")
strConn = "Driver={SQL Server};Server=" & strInstance & ";Database=master"
cxn.ConnectionString = strConn
cxn.Open
Set rs=cxn.Execute("SELECT @@version")
if not rs.eof then
arr_1 = split(rs(0).value,"-")
arr_2 = split(arr_1(1)," ")
res.writeline("!!sql_instance==" & strInstance & "==" & arr_2(1))
end if
next
end if
end sub
Hi, that's great. Thanks for the grade, and thanks for posting your working code.
Regards,
Rob.
Regards,
Rob.
A simplest way to get the instances:
str = "localhost"
Set objSQLDMOApp = CreateObject("SQLDMO.SQLServer2")
set objInstances = objSQLDMOApp.ListInstalledInstances(str)
For Count=1 To objInstances.Count
WScript.Echo objInstances.Item(count)
Next
Open in new window