SQL Server Central is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
Search:  
 
Home Articles Editorials Forums Scripts Blogs QotD SQL Jobs Training Authors About us Contact us Newsletters Advertise Write for us
 

Ways to Determine the Version and SP of SQL Server

By Dinesh Priyankara, 2003/02/28

Total article views: 20051 | Views in the last 30 days: 149
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

By Dinesh Priyankara, 2003/02/28

Total article views: 20051 | Views in the last 30 days: 149
Your response
 
  
Related tags
 
Like this? Try these...

Block the DBA?

By Robert Marda | Category: Administering
| 6,365 reads

Optimize Your Applications

By Dinesh Priyankara | Category: Miscellaneous
| 7,514 reads

Creating a System Stored Procedure

By Robert Marda | Category: Administering
| 9,904 reads
Already registered?  

Free registration required

To read the rest of this article, and access thousands of other articles, we ask you to register on the site and subscribe to our newsletters.

Register

E-mail address:
Password:
Password (confirm):

  

Subscriptions

We ask you to register on the site and subscribe to our newsletters. Subscribing to our newsletters gets you:

  • ALL of our content (thousands of articles, scripts, and forum postings)
  • A daily newsletter (example)
  • A weekly news round up (example)
  • The opportunity to ask and answer questions in our forums
  • A daily Question of the Day to test and help you increase your knowledge of SQL Server.

We ask that you give the newsletter a try for a week. Over 200,000 SQL Server Professionals a day find it entertaining and useful. If not, you are welcome to unsubscribe at anytime.

Steve Jones
Editor, SQLServerCentral.com

 
Copyright © 2002-2008 Simple Talk Publishing. All Rights Reserved. Privacy Policy. Terms of Use