FunnY Question About service pack of SQL 7.0 :)

[funcrecall12]

I like to check what Service Pack was already installed to SQL 7.0 ..

This is how I check.

Go to Enterprise Manager and click help and click about ..
So the window display ..

MS Managament Console 1.1
version 4.0(Build I386 Service Pack 4)
Copyright ...
..
..
..
and a button Ok ..

and I assume that the service pack installed was SP4

An I Right?

 

[SQLBill]

Nope, run this command SELECT @@VERSION

That will give you the version number, which for SQL 7.0 will start with 7.00.xxxx and the last set of numbers will be either three or four digits long. That last set of digits tells you what patch/SP you have.

You can go to Microsoft's website and figure out what the numbers mean (I don't know the link) or go to

http://www.sqlsecurity.com/DesktopDefault.aspx?tabindex=3&tabid=4

That takes you to a SQLSecurity Checklist.

-SQLBill

 

[wepisan]

Hi,

try this procedure

CREATE PROCEDURE dbo.usp_get_service_pack_info AS
/*********************************************************************************************************************************************************
** dbo.usp_get_service_pack_info
**
** Description: This Procedure displays the service pack info for the server
**
** Author : Vaiyapuri Subramanian
**
** Date : 01/27/2003
**
** Usage: exec usp_get_service_pack_info
**
** Dependencies: None
**
** Logic:
**
** change log:
**
**
** date who change description
**
**
**********************************************************************************************************************************************************/
BEGIN

-- set the nocount flag to on
SET NOCOUNT ON

-- variable declaration
DECLARE
@sqlServerSix VARCHAR(20), -- Variable to hole v6.0
@sqlServerSixDotFive VARCHAR(20), -- Variable to hole v6.5
@sqlServerSeven VARCHAR(20), -- Variable to hole v7.0
@sqlServerEight VARCHAR(20), -- Variable to hole v8.0 (2000)
@service_pack_number VARCHAR(15), -- Variable to indicate service pack number
@noServicePack VARCHAR(30), -- Variable to indicate no service pack
@servicePack1 VARCHAR(30), -- Variable to indicate service pack 1
@servicePack2 VARCHAR(30), -- Variable to indicate service pack 2
@servicePack3 VARCHAR(30), -- Variable to indicate service pack 3
@servicePack4 VARCHAR(30), -- Variable to indicate service pack 4
@servicePack5 VARCHAR(30), -- Variable to indicate service pack 5
@servicePack5a VARCHAR(30), -- Variable to indicate service pack 6
@servicePack6 VARCHAR(30) -- Variable to indicate service pack 7

-- Assign varsion values to variables
SET @sqlServerSix = 'SQL Server 6.0'
SET @sqlServerSixDotFive = 'SQL Server 6.5'
SET @sqlServerSeven = 'SQL Server 7.0'
SET @sqlServerEight = 'SQL Server 2000'

-- get the version
SET @service_pack_number = RTRIM(SUBSTRING(@@VERSION, 30, 9))

-- Assign service pack values to variables
SET @noServicePack = 'No Service Pack Installed'
SET @servicePack1 = 'Service Pack 1 Installed'
SET @servicePack2 = 'Service Pack 2 Installed'
SET @servicePack3 = 'Service Pack 3 Installed'
SET @servicePack4 = 'Service Pack 4 Installed'
SET @servicePack5 = 'Service Pack 5 Installed'
SET @servicePack5a = 'Service Pack 5a Installed'
SET @servicePack6 = 'Service Pack 6 Installed'

-- display the result
SELECT
CASE
WHEN @service_pack_number = '6.00.121' THEN @sqlServerSix + ' - ' + @noServicePack + '(' + @service_pack_number + ')'
WHEN @service_pack_number = '6.00.124' THEN @sqlServerSix + ' - ' + @servicePack1 + '(' + @service_pack_number + ')'
WHEN @service_pack_number = '6.00.139' THEN @sqlServerSix + ' - ' + @servicePack2 + '(' + @service_pack_number + ')'
WHEN @service_pack_number = '6.00.151' THEN @sqlServerSix + ' - ' + @servicePack3 + '(' + @service_pack_number + ')'

WHEN @service_pack_number = '6.50.201' THEN @sqlServerSixDotFive + ' - ' + @noServicePack + '(' + @service_pack_number + ')'
WHEN @service_pack_number = '6.50.213' THEN @sqlServerSixDotFive + ' - ' + @servicePack1 + '(' + @service_pack_number + ')'
WHEN @service_pack_number = '6.50.240' THEN @sqlServerSixDotFive + ' - ' + @servicePack2 + '(' + @service_pack_number + ')'
WHEN @service_pack_number = '6.50.258' THEN @sqlServerSixDotFive + ' - ' + @servicePack3 + '(' + @service_pack_number + ')'
WHEN @service_pack_number = '6.50.281' THEN @sqlServerSixDotFive + ' - ' + @servicePack4 + '(' + @service_pack_number + ')'
WHEN @service_pack_number = '6.50.415' THEN @sqlServerSixDotFive + ' - ' + @servicePack5 + '(' + @service_pack_number + ')'
WHEN @service_pack_number = '6.50.416' THEN @sqlServerSixDotFive + ' - ' + @servicePack5a + '(' + @service_pack_number + ')'

WHEN @service_pack_number = '7.00.623' THEN @sqlServerSeven + ' - ' + @noServicePack + '(' + @service_pack_number + ')'
WHEN @service_pack_number = '7.00.699' THEN @sqlServerSeven + ' - ' + @servicePack1 + '(' + @service_pack_number + ')'
WHEN @service_pack_number = '7.00.842' THEN @sqlServerSeven + ' - ' + @servicePack2 + '(' + @service_pack_number + ')'
WHEN @service_pack_number = '7.00.961' THEN @sqlServerSeven + ' - ' + @servicePack3 + '(' + @service_pack_number + ')'
WHEN @service_pack_number = '7.00.1063' THEN @sqlServerSeven + ' - ' + @servicePack4 + '(' + @service_pack_number + ')'

WHEN @service_pack_number = '8.00.194' THEN @sqlServerEight + ' - ' + @noServicePack + '(' + @service_pack_number + ')'
WHEN @service_pack_number = '8.00.384' THEN @sqlServerEight + ' - ' + @servicePack1 + '(' + @service_pack_number + ')'
WHEN @service_pack_number = '8.00.534' THEN @sqlServerEight + ' - ' + @servicePack2 + '(' + @service_pack_number + ')'
WHEN @service_pack_number = '8.00.760' THEN @sqlServerEight + ' - ' + @servicePack3 + '(' + @service_pack_number + ')'
END [Service Pack & Version Information]

END
GO


To run just type
EXEC usp_get_service_pack_info

Hope this helps.

Thanks,
Vaiyapuri Subramanian

 

[funcrecall12]

Oooppss . I already have a mistake ..
I already told them its already SP4 ..
I have to apply it now ...