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 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 accepte
Thanks in Advanc
Cheer
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 nee
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 log via WMI for the event that SQL records on startup, which give the version of SQL, and also the version of SQL Server that is running (Enterprise / Standard /Developer etc)...the only limitation on this is that you need a relatively specific WQL (WMI Query Language) query - as its an information event that only differs in the message 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 to grab the SQL product type (enterprise edition etc) at the same time.....is this something that is also stored in the registry, as the only reference that might give this info is the productID (HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\80\Registration), which I'll check on some different version machines to see if it actaully does identify 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, otherwise I'll post the results of what I find out back here for your referenc
Thanks agai
cheer
Iain|||Rasmus,
This should help:
How do I know which version of SQL Server I'm running?
http://aspfaq.com/show.asp?id=2160
--
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Rasmus wrote:
> Ok, CSDVersion gives me "8.00.760" - what does that tell me about the Service Pack installed?
>
> Regards|||Thanks Mark
I can use the list of version numbers - but what a crappy way of encoding the SP version into the version number.
It's impossible for me to write an algorithm displaying the SP version - that'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?