Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 


 

Ways to Determine the Version and SP of SQL Server

By Dinesh Priyankara, 2003/02/28

How to Determine the Version and Service Pack.

By: Dinesh Priyankara

Recently 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).

SQL Server 2000 version and level

@@VERSION

Product Level

SQL Server 2000 RTM

8.00.194

RTM

Database Components SP1

8.00.384

SP1

Database Components SP2

8.00.534

SP2

Database Components SP3

8.00.760

SP3


Now 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
Total article views: 21833 | Views in the last 30 days: 16
 
Related Articles
FORUM

SELECT @@VERSION

SELECT @@VERSION

BLOG

How to Read the Results of SELECT @@VERSION in SQL Server

The simple query SELECT @@VERSION can quickly tell you quite a bit about a SQL Server installation (...

FORUM

Reporting Services Installation Problem - help requested

SQL Server 2005 Reporting Services installation help

FORUM

Service pack 2 installation

Reporting Services authentication issue during installation

FORUM

Post Sql Installation

services installed

Tags
administration    
configuring    
installation    
service packs    
sql server 7    
t-sql    
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones
 
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.