SQL Server Version and SP

(imported topic written by SystemAdmin)

Has anyone created property relevance to determine if SQL Server is installed and what version/service pack is being used?

(imported comment written by brolly3391)

Hello Nicky,

I did a few quick Google searches on this and came up with this:

value “CurrentVersion” of key “HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\MSSQLServer\CurrentVersion” of registry

or for a more granular version:

value “CSDVersion” of key “HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\MSSQLServer\CurrentVersion” of registry

lookup table here

http://support.microsoft.com/kb/321185

(strip the 2000. or 2005. prefix off for the registry version equivalent)

better lookup table here

http://www.krell-software.com/mssql-builds.asp

as an easy retrieved property, I would wrap some existence checks around this for something like this:

q: if (exists key “HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\MSSQLServer\CurrentVersion” whose ( exists value “CSDVersion” of it) of registry) then (value “CSDVersion” of key “HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\MSSQLServer\CurrentVersion” of registry) as string else (“No SQL Server Installed”)

A: 8.00.761

T: 0.277 ms

I: singular string

It is possible to perform the lookup using relevance but with that many table entries it will get pretty long and convoluted and every time there was a new one added you would have to add that to your relevance. For simplicity and supportability I would just keep the tables handy and do the lookup manually.

Cheers,

Brolly

(imported comment written by rkc91)

you can do it using

if rope (image path of service “MSSQLSERVER” as string) contains “%22 -sMSSQLSERVER” then version of file (preceding text of last “%22 -sMSSQLSERVER” of following text of position 1 of (image path of service “MSSQLSERVER” as string) ) as string else version of file (image path of service “MSSQLSERVER”) as string

with relevance

exists running service “MSSQLSERVER”

(imported comment written by mgelmer23)

Any chance someone wants to tackle tweaking this to bring back both default and non-default instances, returning a multi-valued property with the instance name (or “default”) and the version?

(imported comment written by SystemAdmin)

I have one for the instances, but not with the version. The “mssql database” object doesn’t have a version, but you could look for the version of the sql server regapp.

Q: ( "Computer: " & computer name & " has SQL instance " & instance name of it as string & ", which is " & ( if ( running of it = true ) then ( “running” ) else ( “not running” ) ) ) of local mssql databases

A: Computer: GPGBYQPC10A has SQL instance GPS5, which is running

A: Computer: GPGBYQPC10A has SQL instance GPS6, which is not running

A: Computer: GPGBYQPC10A has SQL instance GPS7, which is running

But, you know, you could call me about this one. :slight_smile:

Jim

(imported comment written by ErinC91)

Thank you so much for this retrieved property, it’s saved me some valuable time. jnharry’s is exactly what I was looking for! :smiley:

(imported comment written by cstoneba)

the property above to return the SQL version only appears to work if the deafult SQL instance name of MSSQLServer is being used. In the event that multiple SQL version are installed, or the default instance name is not being used, how can the SQL version(s) be returned?