Detecting SQL Server version

[SteveHo]

What is the best way to detect what version of Microsoft SQL Server the server is running?

[_doog_]

well there is no simple answer for this

if you do have a connection, i would execute xp_msver with ProductVersion
i think that is the most relyable way

if you do want to check the files, you have to keep in mind that with sqlserver 2000+ you can install more than one instance on the same machine and every instance can have a different version

* so you would have to first determine the instance name you are looking for and
* second determine the version number of the instance

i would use the sqldmo object for this...

[Frank Horn]

I'm not sure if using DMO is a good idea when you don't know what's installed on the target system. Could be MSDE without DMO, or some older DMO version, and what happens if you install your DMO on top of that?

You could try to read the HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\MSSQLServer\CurrentVersion registry key.

[_doog_]

there is a function called PingSQLServerVersion which should work with all versions of the dmo

there is also a sample called dmoping showing the implementation of this (see sql server books online)

When searching the registry, you have to also look for different instances... (i could install my sql-server as instance foo and never have a default instance...)

you would have to search
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server
value: InstalledInstances
to find a space seperated list of the installed instances

with a instance called foo, your key would be
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\foo\MSSQLServer\CurrentVersion

only with the default instance, the key would be as listed above
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\MSSQLServer\CurrentVersion


would be interesting to know, what version you do want to have
major version of sql-server; service-pack or patch level of the instance?