Consider the following scenario:
Ø You have multiple SQL Server instances in your environment (e.g. dozens or maybe hundreds).
Ø You want to find out if some of them are not patched appropriately.
This is what the following vbscript is doing:
Ø Locates all SQL Server instances in your network that are reachable from your machine.
Ø For each of these instances it calculates the SQL build.
Ø Then according to what values you input to it, it reports unsupported builds.
Here is the script:
Dim App1
Dim List
Dim Item
SQLName = "X"
SQLversion = "X"
tempVer = "X"
SQLbuild = "X"
SQL2000SupportedBuild = 2039 'SQL 2000 SP4
SQL2005SupportedBuild = 4035 'SQL 2005 SP3
SQL2008SupportedBuild = 2531 'SQL 2008 SP1
Set App1 = CreateObject("SQLDMO.Application")
Set List = App1.ListAvailableSQLServers()
On Error Resume Next
For Each Item In List
Set objSQLServer = CreateObject("SQLDMO.SQLServer")
objSQLServer.LoginSecure = True
objSQLServer.Connect Item
SQLName = objSQLServer.Name
SQLversion = objSQLServer.VersionString
tempVer = RTrim(LTrim(Mid(objSQLServer.VersionString,21,6)))
If (tempVer = "2000") Then
WScript.Echo tempver
SQLbuild = RTrim(LTrim(Mid(objSQLServer.VersionString,35,4)))
If (CInt(SQLbuild) < SQL2000SupportedBuild) Then
WScript.Echo "SQL Server " & SQLName & " has a build of " & SQLBuild & " which is not supported (lower than " & SQL2000SupportedBuild & ")."
Else
WScript.Echo "SQL Server " & SQLName & " has a build of " & SQLBuild & " which is supported (greater than or equal to " & SQL2000SupportedBuild & ")."
End If
If (tempVer = "2005") Then
SQLbuild = RTrim(LTrim(Mid(objSQLServer.VersionString,34,4)))
If (CInt(SQLbuild) < SQL2005SupportedBuild) Then
WScript.Echo "SQL Server " & SQLName & " has a build of " & SQLBuild & " which is not supported (lower than " & SQL2005SupportedBuild & ")."
WScript.Echo "SQL Server " & SQLName & " has a build of " & SQLBuild & " which is supported (greater than or equal to " & SQL2005SupportedBuild & ")."
If (tempVer = "2008") Then
SQLbuild = RTrim(LTrim(Mid(objSQLServer.VersionString,40,4)))
If (CInt(SQLbuild) < SQL2008SupportedBuild) Then
WScript.Echo "SQL Server " & SQLName & " has a build of " & SQLBuild & " which is not supported (lower than " & SQL2008SupportedBuild & ")."
WScript.Echo "SQL Server " & SQLName & " has a build of " & SQLBuild & " which is supported (greater than or equal to " & SQL2008SupportedBuild & ")."
Next
App1.Quit
Set App1 = Nothing
Wscript.Quit
How to use it:
Ø Save the script above as a .vbs file e.g. called IDunpatchedSQL.vbs
Ø Give appropriate builds for comparison to these variables:
o SQL2000SupportedBuild = 2039 'SQL 2000 SP4
o SQL2005SupportedBuild = 4035 'SQL 2005 SP3
o SQL2008SupportedBuild = 2531 'SQL 2008 SP1
Ø Execute the script and save the results to a txt file:
Cscript IDunpatchedSQL.vbs > report.txt
Executing this script in my lab environment gave this output:
Microsoft (R) Windows Script Host Version 5.6
Copyright (C) Microsoft Corporation 1996-2001. All rights reserved.
SQL Server (local) has a build of 4035 which is supported (greater than or equal to 4035).
SQL Server WIN03SQL05\SQL2K has a build of 2039 which is supported (greater than or equal to 2039).
SQL Server WIN03SQL05\SQL2K8 has a build of 1600 which is not supported (lower than 2531).
Oops, I need to patch my SQL Server 2008 :)
Disclaimer
This is a personal weblog. The opinions expressed here represent my own and not those of my employer. The steps are provided “as is” without a warranty of any kind.