3

Running following query:

SELECT 
    SERVERPROPERTY('productversion') AS version,
    SERVERPROPERTY('ProductMajorVersion') AS major, 
    SERVERPROPERTY('ProductMinorVersion') AS minor;

produces the following results:

version      major   minor
------------ ------- -------
10.0.2531.0  NULL    NULL

What am I missing?

7

The ProductMajorVersion and ProductMinorVersion properties are only available in SQL Server 2012 and above (according to https://docs.microsoft.com/en-us/sql/t-sql/functions/serverproperty-transact-sql). Your product version indicates that this is SQL Server 2008 SP1, which does not support the two other properties.

2

Community wiki answer

One can use PARSENAME to extract the major and minor versions from ProductVersion pre-SQL Server 2012:

PARSENAME(CAST(SERVERPROPERTY('productversion') AS varchar(20)), 4) AS major,
PARSENAME(CAST(SERVERPROPERTY('productversion') AS varchar(20)), 3) AS minor

The PARSENAME method should work regardless of version, but you could COALESCE to use the major/minor property when available:

SELECT 
    SERVERPROPERTY('productversion') AS version,
    COALESCE
    (
        SERVERPROPERTY('ProductMajorVersion'), 
        PARSENAME(CAST(SERVERPROPERTY('productversion') AS varchar(20)), 4)
    ) AS major, 
    COALESCE
    (
        SERVERPROPERTY('ProductMinorVersion'), 
        PARSENAME(CAST(SERVERPROPERTY('productversion') AS varchar(20)), 3)
    ) AS minor;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.