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

Your email address will not be revealed on this site.
PoorExcellent
(Line breaks become <br />)
(For my next comment on this site)
(Allow users to contact me through a message form -- Your email will not be revealed!)
Trackback address for this post

http://www.insidesql.org/blogs/htsrv/trackback.php?tb_id=179

  • 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...