Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 


 
     
Recent Posts
   
Popular Topics
Home   
Search
    Members    Calendar    Who's On

Home » SQL Server 7,2000 » Administration » What is the BEST way to determine the REAL...
Add to briefcase
13 posts, Page 1 of 2
12»»

What is the BEST way to determine the REAL version of SQL 2000?? Expand / Collapse
Rate Topic
Display Mode
Topic Options
Author
Message
subs99
Posted Thursday, November 12, 2009 12:33 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, September 01, 2011 6:31 AM
Points: 21, Visits: 192
So I just inherited a server I must administer. It runs Windows Server 2003 and SQL Server 2000 -- version I'm Not Sure.

I ran 3 different scripts, and even looked at the SQL executable to try to definitively determine the version/SP level.

The main problem I have is: What exactly is the Service Pack applied to this instance of SQL Server 2000?

The first query reports SP2, the second query does not report a SP, and the third query reports SP4. What is going on?!

The only thing consistent in all of them is that they all say 8.00.2055. (searching for this on Microsoft.com gives an article that does NOT seem to shed any light on the SP version: http://support.microsoft.com/kb/959420 )

This article from Microsoft is extremely UN-helpful, especially as it says near the bottom to use @@VERSION.
http://support.microsoft.com/kb/321185/

How do you tell -- definitively -- the SP version? My instance returns SP2 and SP4!

Oh, and what-the-heck does Hotfix 2280 mean? (nothing on Microsoft.com!)



First, here are the results of my scripts:

----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Microsoft SQL Server 2000 - 8.00.2055 (Intel X86)
Dec 16 2008 19:46:53
Copyright (c) 1988-2003 Microsoft Corporation
Enterprise Edition on Windows NT 5.2 (Build 3790: Service Pack 2)


(1 row(s) affected)

Index Name Internal_Value Character_Value
------ -------------------------------- -------------- ------------------------------------------------------------------------------------------------------------------------
1 ProductName NULL Microsoft SQL Server
2 ProductVersion 524288 8.00.2055
3 Language 1033 English (United States)
4 Platform NULL NT INTEL X86
5 Comments NULL NT INTEL X86
6 CompanyName NULL Microsoft Corporation
7 FileDescription NULL SQL Server Windows NT
8 FileVersion NULL 2000.080.2055.00 Hotfix 2280
9 InternalName NULL SQLSERVR
10 LegalCopyright NULL © 1988-2004 Microsoft Corp. All rights reserved.
11 LegalTrademarks NULL Microsoft® is a registered trademark of Microsoft Corporation. Windows(TM) is a trademark of Microsoft Corporation
12 OriginalFilename NULL SQLSERVR.EXE
13 PrivateBuild NULL NULL
14 SpecialBuild 134676480 NULL
15 WindowsVersion 248381957 5.2 (3790)
16 ProcessorCount 8 8
17 ProcessorActiveMask 255 000000ff
18 ProcessorType 586 PROCESSOR_INTEL_PENTIUM
19 PhysicalMemory 9727 9727 (10199801856)
20 Product ID NULL NULL

(20 row(s) affected)

ColumnName ColumnValue
--------------------------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
BuildClrVersion NULL
Collation SQL_Latin1_General_CP1_CI_AS
CollationID 872468488
ComparisonStyle 196609
ComputerNamePhysicalNetBIOS NULL
Edition Enterprise Edition
EditionID NULL
EngineEdition 3
InstanceName NULL
IsClustered 1
IsFullTextInstalled 1
IsIntegratedSecurityOnly 0
IsSingleUser 0
LCID 1033
LicenseType
MachineName
NumLicenses
ProcessID 4472
ProductVersion 8.00.2055
ProductLevel SP4
ResourceLastUpdateDateTime NULL
ResourceVersion NULL
ServerName
SqlCharSet 1
SqlCharSetName NULL
SqlSortOrder 52
SqlSortOrderName NULL

(27 row(s) affected)


I also right-clicked on the actual SQL executable sqlservr.exe, and at the top part it says,

File version: 2000.80.2055.0

and under File Version it has a Value of:

2000.080.2055.00 Hotfix 2280






Here are the 3 scripts I ran:

--Query 1:
SELECT @@VERSION

--Query 2:
EXEC xp_msver

--Query 3:
SELECT 'BuildClrVersion' ColumnName, SERVERPROPERTY('BuildClrVersion') ColumnValue
UNION ALL
SELECT 'Collation', SERVERPROPERTY('Collation')
UNION ALL
SELECT 'CollationID', SERVERPROPERTY('CollationID')
UNION ALL
SELECT 'ComparisonStyle', SERVERPROPERTY('ComparisonStyle')
UNION ALL
SELECT 'ComputerNamePhysicalNetBIOS', SERVERPROPERTY('ComputerNamePhysicalNetBIOS')
UNION ALL
SELECT 'Edition', SERVERPROPERTY('Edition')
UNION ALL
SELECT 'EditionID', SERVERPROPERTY('EditionID')
UNION ALL
SELECT 'EngineEdition', SERVERPROPERTY('EngineEdition')
UNION ALL
SELECT 'InstanceName', SERVERPROPERTY('InstanceName')
UNION ALL
SELECT 'IsClustered', SERVERPROPERTY('IsClustered')
UNION ALL
SELECT 'IsFullTextInstalled', SERVERPROPERTY('IsFullTextInstalled')
UNION ALL
SELECT 'IsIntegratedSecurityOnly', SERVERPROPERTY('IsIntegratedSecurityOnly')
UNION ALL
SELECT 'IsSingleUser', SERVERPROPERTY('IsSingleUser')
UNION ALL
SELECT 'LCID', SERVERPROPERTY('LCID')
UNION ALL
SELECT 'LicenseType', SERVERPROPERTY('LicenseType')
UNION ALL
SELECT 'MachineName', SERVERPROPERTY('MachineName')
UNION ALL
SELECT 'NumLicenses', SERVERPROPERTY('NumLicenses')
UNION ALL
SELECT 'ProcessID', SERVERPROPERTY('ProcessID')
UNION ALL
SELECT 'ProductVersion', SERVERPROPERTY('ProductVersion')
UNION ALL
SELECT 'ProductLevel', SERVERPROPERTY('ProductLevel')
UNION ALL
SELECT 'ResourceLastUpdateDateTime', SERVERPROPERTY('ResourceLastUpdateDateTime')
UNION ALL
SELECT 'ResourceVersion', SERVERPROPERTY('ResourceVersion')
UNION ALL
SELECT 'ServerName', SERVERPROPERTY('ServerName')
UNION ALL
SELECT 'SqlCharSet', SERVERPROPERTY('SqlCharSet')
UNION ALL
SELECT 'SqlCharSetName', SERVERPROPERTY('SqlCharSetName')
UNION ALL
SELECT 'SqlSortOrder', SERVERPROPERTY('SqlSortOrder')
UNION ALL
SELECT 'SqlSortOrderName', SERVERPROPERTY('SqlSortOrderName')
Post #818052
Bru Medishetty
Posted Thursday, November 12, 2009 12:39 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Monday, February 11, 2013 8:27 AM
Points: 1,735, Visits: 1,947

Microsoft SQL Server 2000 - 8.00.2055 (Intel X86)
Dec 16 2008 19:46:53
Copyright (c) 1988-2003 Microsoft Corporation
Enterprise Edition on Windows NT 5.2 (Build 3790: Service Pack 2)


You got it wrong the SP2 it says is for Windows NT 5.2 That is Windows Server 2003.

What does the following Script display at your server?
SELECT 'ProductLevel', SERVERPROPERTY('ProductLevel')



That is the SP level for SQL 2000.

I ran in my system and it has the same 8.00.2055 and it is SP4.

Bru Medishetty

Blog -- LearnSQLWithBru

Join on Facebook Page Facebook.com\LearnSQLWithBru

Twitter -- BruMedishetty
Post #818055
subs99
Posted Thursday, November 12, 2009 12:49 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, September 01, 2011 6:31 AM
Points: 21, Visits: 192
Bru Medishetty (11/12/2009)

Microsoft SQL Server 2000 - 8.00.2055 (Intel X86)
Dec 16 2008 19:46:53
Copyright (c) 1988-2003 Microsoft Corporation
Enterprise Edition on Windows NT 5.2 (Build 3790: Service Pack 2)

... SP2 it says is for Windows NT 5.2 That is Windows Server 2003.


Lesson Learned:

Do not accidentally do drugs prior to working on SQL Server.
Post #818065
GilaMonster
Posted Thursday, November 12, 2009 12:53 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 3:21 AM
Points: 37,734, Visits: 30,002
The output of @@Versions confuses so many people. The different colours show what's referring to what.

Microsoft SQL Server 2000 - 8.00.2055 (Intel X86)
Dec 16 2008 19:46:53
Copyright (c) 1988-2003 Microsoft Corporation
Enterprise Edition on Windows NT 5.2 (Build 3790: Service Pack 2)


Version 2055 appears to be SP4 with some additional hotfixes applied.
http://www.sqlsecurity.com/FAQs/SQLServerVersionDatabase/tabid/63/Default.aspx is pretty good and lists SP4 as 2039, you you're a little above that.

Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass
Post #818067
Joie Andrew
Posted Thursday, November 12, 2009 2:30 PM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Today @ 3:15 AM
Points: 550, Visits: 1,028
8.00.2055 is SP4 with MS09-004 applied.

http://support.microsoft.com/kb/959420

Joie Andrew
"Since 1982"
Post #818132
subs99
Posted Friday, November 13, 2009 8:00 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, September 01, 2011 6:31 AM
Points: 21, Visits: 192
Thanks for the replies, but just so I'm clear on this -- there is no official Microsoft list of all the possible version of SQL 2000, right?

For example, I found this so called "Cumulative" list of the hotfixes that are available for SQL Server 2000 SP4...

http://support.microsoft.com/kb/894905

...but that list does NOT include my particular version -- 8.00.2055 -- thanks Microsoft!

Here are two other unofficial lists (but a lot more like Microsoft should have!) and neither of them contains my version either...

http://www.sqlsecurity.com/FAQs/SQLServerVersionDatabase/tabid/63/Default.aspx

http://www.sqlteam.com/article/sql-server-versions

What the heck man?!
Post #818519
Joie Andrew
Posted Friday, November 13, 2009 12:45 PM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Today @ 3:15 AM
Points: 550, Visits: 1,028
Yeah, I think the big problem for keeping up with the lists is the complexity of the software involved. For example, you have a vulnerability that comes out which is tracked by an MS number. That MS number can lead to multiple KB (or Q for the old numbering system) patches for multiple platforms, such as SQL 2000, 2005, MSDE, and so on. For each of those, depending on the patch type, either GDR or QFE, you can also get a different build number, because it is essentially a different package. I think because of all of this and the fact that major SQL 2000 support stopped in 2008, it is going to be hard to find a "definitive" list of patches. You can ping Microsoft about it though, and they may be able to get you a more detailed one.

For me though, I just worry about the range of numbers that the build is in. Based on [url=http://support.microsoft.com/kb/321185][/url] you know that SP 4 started at 2000.8.00.2039. Everything past that should indicate a modification to SQL Server SP4. If you want specifics of what was loaded, you should be able to look up each SQL hotfix applied in Add/Remove Programs, ensuring the checkbox to show updates is shown. Based on the hotfix numbers shown in Add/Remove Programs you should be able to lookup the specific patch and MS vulnerability information for everything loaded since SP4, and it should give you the individual build numbers each one would have left SQL in after the install.

Joie Andrew
"Since 1982"
Post #818823
MANU-J.
Posted Friday, November 13, 2009 9:18 PM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: 2 days ago @ 1:56 PM
Points: 1,658, Visits: 8,564
8.00.2055 SQL 2000 Service Pack 4 + Security Update KB960082


http://www.bigdatabaselist.com/wiki/SQL_Server_Version_Builds

HTH!

Manu
Post #818947
subs99
Posted Monday, November 16, 2009 11:46 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, September 01, 2011 6:31 AM
Points: 21, Visits: 192
MANU-296622 (11/13/2009)
8.00.2055 SQL 2000 Service Pack 4 + Security Update KB960082


http://www.bigdatabaselist.com/wiki/SQL_Server_Version_Builds

HTH!

Manu


Thanks, that helps!

I still think Microsoft should be the ones to create a database of Microsoft database versions!! (silly me)

Just FYI, I now have 3 (non-Microsoft) lists of Microsoft SQL Server versions:

1. http://www.bigdatabaselist.com/wiki/SQL_Server_Version_Builds
2. http://www.sqlteam.com/article/sql-server-versions
3. http://www.sqlsecurity.com/FAQs/SQLServerVersionDatabase/tabid/63/Default.aspx
Post #819575
payal.dave
Posted Tuesday, November 17, 2009 8:43 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Friday, June 29, 2012 3:32 AM
Points: 207, Visits: 57
SELECT SERVERPROPERTY('productversion'), SERVERPROPERTY
('productlevel'), SERVERPROPERTY ('edition')
Post #820127
« Prev Topic | Next Topic »

Add to briefcase
13 posts, Page 1 of 2
12»»

Permissions Expand / Collapse

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