This script should get you pretty close.
Kevin Martin
Database Engineer
Multimedia Games, Inc.
SET NOCOUNT ON
SELECT CONVERT(CHAR(20),@@SERVERNAME) AS 'ServerName',
SUBSTRING(@@VERSION,23,4) AS 'SQLVersion',
SUBSTRING(@@VERSION,30,9) AS 'BuildNumber',
CASE SUBSTRING(@@VERSION,35, 4)
-- 6.5
WHEN '121 ' THEN 'NO SP'
WHEN '124 ' THEN 'SP1'
WHEN '139 ' THEN 'SP2'
WHEN '151 ' THEN 'SP3'
WHEN '201 ' THEN 'NO SP'
WHEN '213 ' THEN 'SP1'
WHEN '240 ' THEN 'SP2'
WHEN '252 ' THEN 'SP3 ** BAD **'
WHEN '258 ' THEN 'SP3'
WHEN '259 ' THEN 'SP3 + SBS'
WHEN '281 ' THEN 'SP4'
WHEN '297 ' THEN 'SP4 + SBS'
WHEN '339 ' THEN 'SP4 + Y2K'
WHEN '415 ' THEN 'SP5 ** BAD **'
WHEN '416 ' THEN 'SP5a'
-- 7.0
WHEN '198 ' THEN 'Beta 1'
WHEN '517 ' THEN 'Beta 3'
WHEN '583 ' THEN 'RC1'
WHEN '623 ' THEN 'NO SP'
WHEN '689 ' THEN 'SP1 Beta'
WHEN '699 ' THEN 'SP1'
WHEN '835 ' THEN 'SP2 Beta'
WHEN '842 ' THEN 'SP2'
WHEN '961 ' THEN 'SP3'
WHEN '1021' THEN 'SP3 plus MS02-007'
WHEN '1030' THEN 'SP3 plus MS02-020'
WHEN '1063' THEN 'SP4'
WHEN '1077' THEN 'SP4 plus MS02-056'
WHEN '1078' THEN 'SP4 plus MS02-061'
-- 2000
WHEN '194 ' THEN 'NO SP'
WHEN '384 ' THEN 'SP1'
WHEN '532 ' THEN 'SP2'
WHEN '578 ' THEN 'SP2 plus MS02-007'
WHEN '608 ' THEN 'SP2 plus MS02-020'
WHEN '636 ' THEN 'SP2 plus MS02-029'
WHEN '650 ' THEN 'SP2 plus MS02-034'
WHEN '655 ' THEN 'SP2 plus MS02-038'
WHEN '679 ' THEN 'SP2 plus MS02-056'
WHEN '686 ' THEN 'SP2 plus MS02-061'
WHEN '760 ' THEN 'SP3'
ELSE 'Unknown '
END AS 'ServicePack'
-----Original Message-----
From: Adil Basha [mailto:[Email Address Removed] Wednesday, December 17, 2003 4:22 PM
To: LazyDBA.com Discussion
Subject: version and service pack of sql server
hi all,
Sql Server 7
Pls can u give command where i can be able to find out
the version and service pack of the servers which i am
using.
and also a script will when run would give the
solution
pls. its very urgent help me....
TIA
Adil
---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.552 / Virus Database: 344 - Release Date: 12/15/2003
MS Sql Server LazyDBA home page