Pack.

one of my co-workers came and asked me the correct version of SQL Server

that we have installed in our company and the service pack. So, I asked

him to run the SELECT @@VERSION in the Query Analyzer and see the result

to get the version and service pack installed. He ran the query and got

the result but still he could not get the correct service pack installed

but version.

This is because by looking at the version (major,

minor and revision) given by the result, the correct service pack

installed cannot be determined.

So, I explained him the way to read

the service pack and decided to share this with you all as you might

unaware with these facts.

First let’s run the query and get the

result.

SELECT

@@VERSION

Output:
Microsoft

SQL Server 2000 - 8.00.760 (Intel X86)
    Dec 17

2002 14:22:05
    Copyright (c) 1988-2003

Microsoft Corporation
    Enterprise Edition on

Windows NT 5.0 (Build 2195: Service Pack 2)

This

gives you the version (as 8.00.760) and the edition of SQL Server

installed but service pack. The following table shows the relationship

between version and product level (SP).

you can see the version 8.00.760 represents the service pack

3.

Similarity, you can determine this by executing the SELECT

SERVERPROPERTY(property name).

SELECT

SERVERPROPERTY('ProductVersion')

Output:
8.00.760

SELECT

SERVERPROPERTY('ProductLevel')

Output:
SP3

**See

BOL for other properties.

You can get same information with

xp_msver with option or without option.

master..xp_msver

ProductVersion

Output:
Index        Name        Internal_Value        Character_Value
----------------------------------------------------------------------
2        ProductVersion        524288        8.00.760

If

you execute xp_msver without specifing a property, you will get all

information about version.

I highly appreciate all your comments

about this article.

You can reach me through dinesh@dineshpriyankara.com