Programatically find out the SQL Server Version and SP via registry or WMI?

Hi All,
Just keen to find a way to find the version of SQL Server and the current in
stalled SQL Server Service pack via either a registry key or a WMI class ent
ry...
I've had a good look, but can't seem to find anything that matches or is use
ful. I know you can retrieve this via a SQL query (with Select @.@.version), b
ut I'm after a way that uses our existing WMI/registry reading process as we
are collecting info from s
ervers all over the world, so I don't want to have to deal with all the SQL
permissions issues & have to use a different tool in order to run a SQL quer
y...
Any info or advice that people can offer would be greatfully accepted
Thanks in Advance
Cheers
IainHKEY_LOCAL_MACHINE/Software/Microsoft/MSSQLServer/MSSQLServer/CurrentVersion
/CSDVersion
It's for default instance, don't know what will be for named instance really
but you can check it by yourself
"iain sandercock" <iain*DOT*sandercock@.*NOSPAM*riotinto.com> wrote in
message news:8E21AF3D-25DF-4709-AB10-4CD106A57340@.microsoft.com...
> Hi All,
> Just keen to find a way to find the version of SQL Server and the current
installed SQL Server Service pack via either a registry key or a WMI class
entry...
> I've had a good look, but can't seem to find anything that matches or is
useful. I know you can retrieve this via a SQL query (with Select
@.@.version), but I'm after a way that uses our existing WMI/registry reading
process as we are collecting info from servers all over the world, so I
don't want to have to deal with all the SQL permissions issues & have to use
a different tool in order to run a SQL query...
> Any info or advice that people can offer would be greatfully accepted
> Thanks in Advance
> Cheers
> Iain|||Alex,
A named instance can be found here:
HKEY_LOCAL_MACHINE/Software/Microsoft/Microsoft SQL
Server/INSTANCE_NAME/MSSQLServer/CurrentVersion/CSDVersion
Where INSTANCE_NAME is the name of your SQL Server named instance.
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"Alex Cieszinski" <nomail@.thanks> wrote in message
news:ODWl%23YwPEHA.3304@.TK2MSFTNGP12.phx.gbl...
>
HKEY_LOCAL_MACHINE/Software/Microsoft/MSSQLServer/MSSQLServer/CurrentVersion
> /CSDVersion
> It's for default instance, don't know what will be for named instance
really
> but you can check it by yourself
>|||Thanks Mark & Alex...
This is exactly what I need
Can't believe I didn't spot this, must have been due to a long day on friday
for me to have missed it. The other method I found was to check the event l
og via WMI for the event that SQL records on startup, which give the version
of SQL, and also the versi
on of SQL Server that is running (Enterprise / Standard /Developer etc)...th
e only limitation on this is that you need a relatively specific WQL (WMI Qu
ery Language) query - as its an information event that only differs in the m
essage text from a range of
other event log informational messages from SQLServer, and the LIKE operator
for WQL is only suppported in XP/Server2003.....not w2k which the majority
of the servers will be.
Given that this info is in the registry, I wonder why in the PSS collection
tool they retrieve the version from SQL statement, but I assume that it is t
o grab the SQL product type (enterprise edition etc) at the same time.....i
s this something that is al
so stored in the registry, as the only reference that might give this info i
s the productID (HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\M
icrosoft SQL Server\
80\Registration), which I'll check on some different version machines to see
if it actaully does identi
fy the product type...
if you already know if productID correlates with the different editions, and
what the productIDs are for the various editions, that would be great, othe
rwise I'll post the results of what I find out back here for your reference
Thanks again
cheers
Iain|||Ok, CSDVersion gives me "8.00.760" - what does that tell me about the Servic
e Pack installed?
Regards|||Thanks Mark,
I can use the list of version numbers - but what a crappy way of encoding th
e SP version into the version number.
It's impossible for me to write an algorithm displaying the SP version - tha
t'll also work when SP4 i released!
Any ideas?|||... and on a related node:
- how do i retrieve the version and SP of Internet Explorer?