How to determine the version of SQL Server programatically.

What is the official supported way to determine the version of SQL server and installed Service Pack programatically.

Different components such as Analysis Services or SSIS can have service pack applied at different times. So what is the right officially supported way to determine the version of those components.

Thank you in advance.

you can try this,

select @.@.version

select serverproperty('productversion')

you can refer, http://support.microsoft.com/kb/321185

|||

Thank you for the pointer to this kb article. However, I probably did not formulate my question correctly. I would need to determine the version by running some executable in the command line rather than connecting to SQL Server database. For example I can have just SQL server Integration Services installed but not the database itself.

Is there officially supported solution in this case?

|||How do you need to get the value back ? You could use the SQLCMD to execute and retrieve the value to the command line. Perhaps you can elaborate a bit more on the situation and the environment where the applciation is started.

Jens K. Suessmeyer

http://www.sqlserver2005.de

|||

Thank you for your answer. I do understand that I can invoke sqlcmd. However what if database is not installed and all what I do have installed is Integration Services. What should I do in this situation. Besides I can apply the service pack to each of sql server major components separately. So if I do have the database with one version and analysis services with another version I will get simply incorrect value for Analysis Services invoking this command. What I actually need is a supported way to determine the version of the component I do need. Ideally it should work as version command in windows command line for each of the major component of SQL server that can be installed independently of each other. However, I can understand that this expectation might not be realistic. What I actually need is officially supported way so I will not make a mistake determining the version.

|||Try to read the below registry key value, whcih will provide you the version of Database Engine.

HKLM\Software\Microsoft\MSSQLServer\MSSQLServer\CurrentVersion

read the string value "CurrentVersion"

It will provide you the Version number. This can be done without connecting to SQL server.
|||Thank you for the reply. What I can do in the case of Integration Services and Analysis Services in case if database Engine is not installed.|||

Look at the Services registration:

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\Services

Jens K. Suessmeyer

http://www.sqlserver2005.de