August 10, 2004 at 12:14 pm
Even after I just installed SP3a on a server, every type of verification I do just shows SP3.
That includes
-right click and select properties in em
-select serverproperty('productlevel')
-select @@version
Does anyone know an Iron Clad test for determining the exact service pack level of a SQL2K server?
August 10, 2004 at 12:19 pm
Check the registry.
uHKLM\Software\Microsoft\Microsoft SQL Server\<instance Name>\CurrentVersion\CSDversion
August 10, 2004 at 12:29 pm
Thanks!! That works perfectly.
Now, is there anyway to query the same information from the DB itself?
I can work on the regisry in develpment, but in production, that gets a little more tricky.
August 10, 2004 at 12:33 pm
You can use the xp_regread undocumented extended stored procedure to read from the registry via Transact-SQL.
This is the syntax of the xp_regread:
EXECUTE xp_regread [@rootkey=]'rootkey', [@key=]'key' [, [@value_name=]'value_name'] [, [@value=]@value OUTPUT] |
For example, to read into the variable @test-2 from the value 'TestValue' from the key 'SOFTWARE\Test' from the 'HKEY_LOCAL_MACHINE', run:
DECLARE @test varchar(20)EXEC master..xp_regread @rootkey='HKEY_LOCAL_MACHINE', @key='SOFTWARE\Test', @value_name='TestValue', @value=@test OUTPUTSELECT @test |
August 10, 2004 at 2:47 pm
Apparently Microsoft may have forgotten to increase the build number between SP3 and SP3a. For furthur discussion see
http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=48&messageid=122785
If you install MS01-031 the build number goes to 8.00.818.
Francis
August 11, 2004 at 3:40 am
Now, is there anyway to query the same information from the DB itself?
Answer
In EM or QA with @@version
E.G.
Open a table (Select all rows) open the Sql window (click on the SQL button)
Type SELECT @@VERSION AS VERSION en run the SQL
Then you see:
Microsoft SQL Server 20000 - 8.00.760 (IntelX86) Dec 17 14:22 etc.
As Allen says SP3 is 8.00.760 and SP3a is 8.00.761
August 11, 2004 at 7:15 am
Henk,
That won't work as the original poster pointed out. @@VERSION will return 760 regardless of whether you have SP3 or SP3a.
From what I have seen on Microsoft's website, they consider SP3 and SP3a to be the same. You'll find that they say if you already have SP3 installed, there's no need to install SP3a. I'm not sure I agree with that, but that's MS's opinion.
-SQLBill
August 11, 2004 at 7:28 am
If you install sp3 and haven't install hotfix ms03-031, you have to install security fix ms02-061, but you don't have to do that if you install sp3a. There are other fixes in sp3a too. For details, see microsoft website.
August 11, 2004 at 7:59 am
My mistake, Only sp2 need to istall ms02-061.
August 11, 2004 at 11:56 am
Here what I do:
Search for the ssnetlib.dll. In most of the cases it should be inC:\Program Files\Microsoft SQL Server\MSSQL\Binn.
Right Click-->Properties-->Version-->Product Version-->8.00.766
8.00.766 stands for SP3A
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply