November 12, 2009 at 12:33 pm
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:
[font="Verdana"]----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
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)[/font]
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')
November 12, 2009 at 12:39 pm
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.
Blog -- LearnSQLWithBru
Join on Facebook Page Facebook.comLearnSQLWithBru
Twitter -- BruMedishetty
November 12, 2009 at 12:49 pm
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.
😛
November 12, 2009 at 12:53 pm
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
November 12, 2009 at 2:30 pm
8.00.2055 is SP4 with MS09-004 applied.
http://support.microsoft.com/kb/959420
Joie Andrew
"Since 1982"
November 13, 2009 at 8:00 am
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?!
November 13, 2009 at 12:45 pm
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 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"
November 13, 2009 at 9:18 pm
8.00.2055 SQL 2000 Service Pack 4 + Security Update KB960082
http://www.bigdatabaselist.com/wiki/SQL_Server_Version_Builds
HTH!
Manu
November 16, 2009 at 11:46 am
MANU-296622 (11/13/2009)
8.00.2055 SQL 2000 Service Pack 4 + Security Update KB960082http://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
November 17, 2009 at 8:43 am
SELECT SERVERPROPERTY('productversion'), SERVERPROPERTY
('productlevel'), SERVERPROPERTY ('edition')
November 17, 2009 at 9:58 am
payal.dave (11/17/2009)
SELECT SERVERPROPERTY('productversion'), SERVERPROPERTY('productlevel'), SERVERPROPERTY ('edition')
Thanks man!
I thought by making a really long original post with all the details I was being nice and clear or whatever.
But it turns out by making a superJumbo-sized post its just too long to read! :pinch:
I had put those SERVERPROPERTY statements in my super-long SQL query in my original post.
Thanks tho!
November 17, 2009 at 12:20 pm
subs99 (11/17/2009)
I thought by making a really long original post with all the details I was being nice and clear or whatever.But it turns out by making a superJumbo-sized post its just too long to read! :pinch:!
Don't worry. There'll always be those who jump straight to a quick 'answer' without reading all (any) of the initial post or following discussion.
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
October 17, 2012 at 8:22 am
Hi,
I was just doing some audits on databases i've inherited. A useful blog that hasnt been mentioned is:
http://sqlserverbuilds.blogspot.com/
I just compared the build versions and made a note of the service pack using 'SELECT @@VERSION', keeping things simple. That blog contains all the builds and it was matter of just making a note of the latest SQL cumulative updates for each service pack.
Thanks
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply