SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


What is the BEST way to determine the REAL version of SQL 2000??


What is the BEST way to determine the REAL version of SQL 2000??

Author
Message
subs99
subs99
SSC-Enthusiastic
SSC-Enthusiastic (193 reputation)SSC-Enthusiastic (193 reputation)SSC-Enthusiastic (193 reputation)SSC-Enthusiastic (193 reputation)SSC-Enthusiastic (193 reputation)SSC-Enthusiastic (193 reputation)SSC-Enthusiastic (193 reputation)SSC-Enthusiastic (193 reputation)

Group: General Forum Members
Points: 193 Visits: 195
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')
Bru Medishetty
Bru Medishetty
SSCertifiable
SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)

Group: General Forum Members
Points: 5694 Visits: 1950

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
subs99
subs99
SSC-Enthusiastic
SSC-Enthusiastic (193 reputation)SSC-Enthusiastic (193 reputation)SSC-Enthusiastic (193 reputation)SSC-Enthusiastic (193 reputation)SSC-Enthusiastic (193 reputation)SSC-Enthusiastic (193 reputation)SSC-Enthusiastic (193 reputation)SSC-Enthusiastic (193 reputation)

Group: General Forum Members
Points: 193 Visits: 195
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.

:-P
GilaMonster
GilaMonster
SSC Guru
SSC Guru (222K reputation)SSC Guru (222K reputation)SSC Guru (222K reputation)SSC Guru (222K reputation)SSC Guru (222K reputation)SSC Guru (222K reputation)SSC Guru (222K reputation)SSC Guru (222K reputation)

Group: General Forum Members
Points: 222079 Visits: 46285
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, MVP, M.Sc (Comp Sci)
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


Joie Andrew
Joie Andrew
SSCertifiable
SSCertifiable (6.4K reputation)SSCertifiable (6.4K reputation)SSCertifiable (6.4K reputation)SSCertifiable (6.4K reputation)SSCertifiable (6.4K reputation)SSCertifiable (6.4K reputation)SSCertifiable (6.4K reputation)SSCertifiable (6.4K reputation)

Group: General Forum Members
Points: 6365 Visits: 2032
8.00.2055 is SP4 with MS09-004 applied.

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

Joie Andrew
"Since 1982"
subs99
subs99
SSC-Enthusiastic
SSC-Enthusiastic (193 reputation)SSC-Enthusiastic (193 reputation)SSC-Enthusiastic (193 reputation)SSC-Enthusiastic (193 reputation)SSC-Enthusiastic (193 reputation)SSC-Enthusiastic (193 reputation)SSC-Enthusiastic (193 reputation)SSC-Enthusiastic (193 reputation)

Group: General Forum Members
Points: 193 Visits: 195
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?!
Joie Andrew
Joie Andrew
SSCertifiable
SSCertifiable (6.4K reputation)SSCertifiable (6.4K reputation)SSCertifiable (6.4K reputation)SSCertifiable (6.4K reputation)SSCertifiable (6.4K reputation)SSCertifiable (6.4K reputation)SSCertifiable (6.4K reputation)SSCertifiable (6.4K reputation)

Group: General Forum Members
Points: 6365 Visits: 2032
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"
MANU-J.
MANU-J.
SSCertifiable
SSCertifiable (7.3K reputation)SSCertifiable (7.3K reputation)SSCertifiable (7.3K reputation)SSCertifiable (7.3K reputation)SSCertifiable (7.3K reputation)SSCertifiable (7.3K reputation)SSCertifiable (7.3K reputation)SSCertifiable (7.3K reputation)

Group: General Forum Members
Points: 7254 Visits: 8766
8.00.2055 SQL 2000 Service Pack 4 + Security Update KB960082


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

HTH!

Manu
subs99
subs99
SSC-Enthusiastic
SSC-Enthusiastic (193 reputation)SSC-Enthusiastic (193 reputation)SSC-Enthusiastic (193 reputation)SSC-Enthusiastic (193 reputation)SSC-Enthusiastic (193 reputation)SSC-Enthusiastic (193 reputation)SSC-Enthusiastic (193 reputation)SSC-Enthusiastic (193 reputation)

Group: General Forum Members
Points: 193 Visits: 195
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
payal.dave
payal.dave
SSC Veteran
SSC Veteran (213 reputation)SSC Veteran (213 reputation)SSC Veteran (213 reputation)SSC Veteran (213 reputation)SSC Veteran (213 reputation)SSC Veteran (213 reputation)SSC Veteran (213 reputation)SSC Veteran (213 reputation)

Group: General Forum Members
Points: 213 Visits: 58
SELECT SERVERPROPERTY('productversion'), SERVERPROPERTY
('productlevel'), SERVERPROPERTY ('edition')
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search