« Determine the last change of the sa password?
SQL Internals Viewer - looking into the SQL Server storage engine »

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


Qui tacet, consentire videtur

serverproperty, service pack, sql server, t-sql, version, xp_msver
SQL Server
 Print article This entry was posted by Torsten Schuessler on 2008-04-03 at 15:29:24 . Follow any responses to this post through RSS 2.0.
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!)
This is a captcha-picture. It is used to prevent mass-access by robots.
Please enter the characters from the image above. (case sensitive)
Trackback address for this post
This is a captcha-picture. It is used to prevent mass-access by robots.
Please enter the characters from the image above. (case sensitive)
  • 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...

Tag cloud

administration backup «best practices» books bug ctp «cumulative update» datetime denali dmv ebook humor index indexoptimize integrity kbfix maintenance «ms sql server 2008» performance php profiler «reporting services» reviews «ross mistry» serverproperty «service pack» «service pack 3» «service packs» sharepoint sp_msforeachdb «sql 2012» «sql pass» «sql server» «sql server 2005» «sql server 2008 r2» «sql server 2012» «sql server builds» sqlcat sqlpass «sqlpass franken» ssms ssmstoolspack «stacia misner» t-sql «technical note» tempdb tools upgrade version whitepapers