Which service pack do I have installed?

Related to previous post's, sometimes it is helpful to quickly determine SQL Server's service pack level.

By selecting @@VERSION in a query window ...

SELECT @@VERSION AS "SQL Server Version"

you / I get following result:

SQL Server Version
Microsoft SQL Server 2005 - 9.00.3054.00 (Intel X86) 
  Mar 23 2007 16:28:52 
  Copyright (c) 1988-2005 Microsoft Corporation
  Developer Edition on Windows NT 6.0 (Build 6000: )

From this, you can quickly determine that I'm running SQL Server 2005 Developer Edition version 9.00.3054.00 on an Intel X86 processor, OS Windows NT 6.0 (Vista). It may also appear - after the Build number - a Service Pack number, but this referes to the operating system and not to the SQL Server system.

To determine SQL Server's service pack level, use the built-in SERVERPROPERTY function. And a little bit more conclusive with appropriate parameters - EDITION, PRODUCTLEVEL, and PRODUCTVERSION.

SELECT SERVERPROPERTY('EDITION')
SELECT SERVERPROPERTY('ProductLevel')
SELECT SERVERPROPERTY('ProductVersion')

The following result appears:

Developer Edition
(1 Zeile(n) betroffen)

SP2
(1 Zeile(n) betroffen)

9.00.3054.00
(1 Zeile(n) betroffen)

And probably you want a deep dive, therfore you can use the extended stored procedure xp_msver [ optname ]

USE master
EXEC xp_msver; --- without optname for the whole result
GO
xp_msver

CU
tosc

No feedback yet
Leave a comment

You must be logged in to leave a comment. Log in now!

If you have no account yet, you can register now...
(It only takes a few seconds!)

  • Torsten Schuessler
    Trackback from: Torsten Schuessler
    2008-06-12 @ 16:56:22

    Microsoft SQL Server 2005 Service Pack 2a ?
    Sometimes i wonder ...
    If you downloaded SP2 prior to 2006-03-05, installing it will yield @@VERSION = 9.00.3042.00.

    AND if you install the hotfix, you will be up to 9.00.3050.00.

    Hotfix description http://support.microsoft.com/kb/933508

    H...