How to find out what Service Packs I have installed for SQL Server 2000

Question

How to find out what Service Packs I have installed for SQL Server 2000

Trystan Hughes

SSCrazy

Points: 2109
More actions
April 10, 2006 at 3:16 am

#111622

Hi all,
How do I find out what service packs I have installed for my copy of Sql Server 2000?
If I go to 'Help->About SQL Server Enterprise Manager' in Enterprise Manager there is nothing in there about what Service Packs I have installed, or what the current one is I have installed.
Thanks
Tryst

Viewing 15 posts - 1 through 15 (of 15 total)

You must be logged in to reply to this topic. Login to reply

sushila SSC-Dedicated Points: 35293 More actions · Answer 1

In QA - set "tools" - "options" - "results to text"...and do

SELECT @@VERSION

**ASCII stupid question, get a stupid ANSI !!!**

Trystan Hughes SSCrazy Points: 2109 More actions · Answer 2

This is what I get. Nothing about what Service Pack I have installed is there...

Microsoft SQL Server 2000 - 8.00.760 (Intel X86)

Dec 17 2002 14:22:05

Developer Edition on Windows NT 5.2 (Build 3790: )

Thanks

Tryst

Steve Jones - SSC Editor SSC Guru Points: 736270 More actions · Answer 3

Service packs are not listed anywhere. You go off the build version and compare it to http://vyaskn.tripod.com/sqlsps.htm

sushila SSC-Dedicated Points: 35293 More actions · Answer 4

hmm..when I do a "select @@version" this is what I get...

Microsoft SQL Server  2000 - 8.00.2039 (Intel X86) 
May  3 2005 23:18:38 
Copyright (c) 1988-2003 Microsoft Corporation
Developer Edition on Windows NT 5.0 (Build 2195: Service Pack 4)


(1 row(s) affected)

**ASCII stupid question, get a stupid ANSI !!!**

sushila SSC-Dedicated Points: 35293 More actions · Answer 5

Trystan - take a look at this...apparently you have sp3..

How to determine which version of SQL Server 2000 is running
To determine which version of SQL Server 2000 is running, connect to SQL Server 2000 by using Query Analyzer, and then run the following code:

SELECT  SERVERPROPERTY('productversion'), SERVERPROPERTY ('productlevel'), SERVERPROPERTY ('edition')

The results are:
•The product version (for example, 8.00.534).
•The product level (for example, "RTM" or "SP2").
•The edition (for example, "Standard Edition"). For example, the result looks similar to:

8.00.534 RTM Standard Edition
The following table lists the Sqlservr.exe version number:
ReleaseSqlservr.exe
RTM2000.80.194.0
SQL Server 2000 SP12000.80.384.0
SQL Server 2000 SP22000.80.534.0
SQL Server 2000 SP32000.80.760.0
SQL Server 2000 SP3a2000.80.760.0
SQL Server 2000 SP42000.8.00.2039

**ASCII stupid question, get a stupid ANSI !!!**

Trystan Hughes SSCrazy Points: 2109 More actions · Answer 6

Yeah, that works sushila, but for some reason SERVERPROPERTY('productversion') and SERVERPROPERTY ('edition') doesn't return anything.

(I have SP 3 - is it a major requirement to jump to SP4?)

Thanks

Tryst

sushila SSC-Dedicated Points: 35293 More actions · Answer 7

Don't believe there's any compelling need - I transitioned over pretty smoothly (from 3 to 4 ie) - but've read several posts here where things that worked with sp3 "broke" after sp4 installation - here's a link to another thread where this is discussed! (& a couple of other links embedded in this link...3 for the price of one)!

**ASCII stupid question, get a stupid ANSI !!!**

sushila SSC-Dedicated Points: 35293 More actions · Answer 8

sorry - forgot to add that the reason you're not seeing anything in the 'productversion' and 'edition' columns is probably because of the length per column setting...if you go to "tools" - "options" and in the "results" tab change the "maximum characters per column" to 50 (or some such) you should see the results...otherwise you just have to scroll far enough to see them - they're there!

**ASCII stupid question, get a stupid ANSI !!!**

Balavenkatesh SSC Rookie Points: 36 More actions · Answer 9

use this query to find the service pack

select serverproperty('productlevel')

Regards,

balavenkatesh

balavenkatesh.sambath@aig.com

Trystan Hughes SSCrazy Points: 2109 More actions · Answer 10

Trystan Hughes

SSCrazy

Points: 2109

April 11, 2006 at 2:39 pm

#631958

ok - sorted!

Thanks for the repies, guys!

Tryst

Harley Arnett-275660 SSC Eights! Points: 859 More actions · Answer 11

The results don't agree??? One query says SP3 - the other SP4??? We appear to be a level in between the levels noted in the chart that was posted:

RTM 2000.80.194.0

SQL Server 2000 SP12000.80.384.0

SQL Server 2000 SP22000.80.534.0

SQL Server 2000 SP32000.80.760.0

SQL Server 2000 SP3a2000.80.760.0

SQL Server 2000 SP42000.8.00.2039

--------------------------------------

select @@version

--------------------------------------

SELECT SERVERPROPERTY('productversion'), SERVERPROPERTY ('productlevel'), SERVERPROPERTY ('edition')

8.00.818 SP3 Enterprise Edition

sushila SSC-Dedicated Points: 35293 More actions · Answer 12

I believe .818 is sp3a + 818 hotfix..

& this from the microsoft site:

"Note: Your product version may be different than these values if you applied a hotfix after installing the product or after installing a previous service pack. For example, @@VERSION returns a value of 8.00.818 after you apply the security fix MS03-031 to SQL Server 2000 SP3a."

what does the "general" tab in your "SQL Server Properties" (EM) box read?!

**ASCII stupid question, get a stupid ANSI !!!**

SQLBill SSC Guru Points: 51440 More actions · Answer 13

Sushila,

You highlighted this line in one of your posts:

on Windows NT 5.0 (Build 2195: Service Pack 4).

I just wanted to point out that is NOT your SQL Server service pack level. That is your WINDOWS OS service pack level.

-SQLBill

SQLBill SSC Guru Points: 51440 More actions · Answer 14

One good site to check out the version number is:

http://www.sqlsecurity.com go to the FAQs/DOCs link and select SQL Server Version Database. Shows every number used and what it equates to (SP and hotfix level). Also lets you know if the hotfix is only available through Microsoft's PSS.

-SQLBill