--Created by MAK
--Date: July 5, 2006
--Find version info of ssnetlib.dll


set nocount on


if substring(@@version,23,4)='2000'
begin
declare @query varchar(1000)
declare @key1 varchar(500)
declare @account varchar(500)
declare @sprealversion varchar(500)
declare @spversion varchar(500)
declare @version varchar(500)
set @version=@@version
set @spversion =ltrim(rtrim(substring(@version,charindex('-',@version)+2,charindex('(Intel X86)',@version)-charindex('-',@version)-2)))


if upper(ltrim(rtrim(@@servicename)))='MSSQLSERVER'
        begin
        set @key1 = 'SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL\setup\'
        end
        else
        begin
        set @key1 = 'SOFTWARE\Microsoft\Microsoft SQL Server\'+upper(ltrim(rtrim(@@servicename)))+'\Setup\'
        end


--print @key1


exec master..xp_regread @rootkey='HKEY_LOCAL_MACHINE',
@key=@key1,
@value_name='SQLPath',
@value=@account output


--print @account
set @query='filever "'+@account+'\BINN\ssnetlib.dll'


create table #tmp (servername varchar(100), servicename varchar(100),sp varchar(100),ssnetlib varchar(600))


insert #tmp(ssnetlib) exec master..xp_cmdshell @query
delete #tmp where ssnetlib is null 



set @sprealversion = CASE @spversion 
 when '9.00.1500'    then '2005 RTM+KB910416' 
 when '9.00.1399.06' then '2005 RTM' 
 when '8.00.2162' then 'SQL Server  2000  SP4+Q904660' 
 when '8.00.2159' then 'SQL Server  2000  SP4+Q907250' 
 when '8.00.2151' then 'SQL Server  2000  SP4+Q903742' 
 when '8.00.2148' then 'SQL Server  2000  SP4+Q899430/31/900390/404/901212/902150/955'
 when '8.00.2145' then 'SQL Server  2000  SP4+Q826906/836651' 
 when '8.00.2040' then 'SQL Server  2000  SP4+Q899761' 
 when '8.00.2039' then 'SQL Server  2000  SP4'  
 when '8.00.2026' then 'SQL Server  2000  SP4 Beta' 
 when '8.00.1025' then 'SQL Server  2000  SP4+Q899428/899430' 
 when '8.00.1021' then 'SQL Server  2000  SP4+Q887700' 
 when '8.00.1017' then 'SQL Server  2000  SP3+Q896425' 
 when '8.00.1014' then 'SQL Server  2000  SP3+Q895123' 
 when '8.00.1013' then 'SQL Server  2000  SP3+Q891866' 
 when '8.00.1009' then 'SQL Server  2000  SP3+Q894257' 
 when '8.00.1007' then 'SQL Server  2000  SP3+Q893312' 
 when '8.00.1003' then 'SQL Server  2000  SP3+Q892923' 
 when '8.00.1001' then 'SQL Server  2000  SP3+Q892205' 
 when '8.00.1000' then 'SQL Server  2000  SP3+Q891585' 
 when '8.00.997'  then 'SQL Server  2000  SP3+Q891311' 
 when '8.00.996'  then 'SQL Server  2000  SP3+Q891017/891268' 
 when '8.00.994'  then 'SQL Server  2000  SP3+Q890942/768/767' 
 when '8.00.993'  then 'SQL Server  2000  SP3+Q890925/888444' 
 when '8.00.991'  then 'SQL Server  2000  SP3+Q889314' 
 when '8.00.990'  then 'SQL Server  2000  SP3+Q890200' 
 when '8.00.988'  then 'SQL Server  2000  SP3+Q889166' 
 when '8.00.985'  then 'SQL Server  2000  SP3+Q889239' 
 when '8.00.980'  then 'SQL Server  2000  SP3+Q887974' 
 when '8.00.977'  then 'SQL Server  2000  SP3+Q888007' 
 when '8.00.973'  then 'SQL Server  2000  SP3+Q884554' 
 when '8.00.972'  then 'SQL Server  2000  SP3+Q885290' 
 when '8.00.970'  then 'SQL Server  2000  SP3+Q872842' 
 when '8.00.967'  then 'SQL Server  2000  SP3+Q878501' 
 when '8.00.962'  then 'SQL Server  2000  SP3+Q883415' 
 when '8.00.961'  then 'SQL Server  2000  SP3+Q873446' 
 when '8.00.959'  then 'SQL Server  2000  SP3+Q878500' 
 when '8.00.957'  then 'SQL Server  2000  SP3+Q870994' 
 when '8.00.955'  then 'SQL Server  2000  SP3+Q867798' 
 when '8.00.954'  then 'SQL Server  2000  SP3+Q843282' 
 when '8.00.952'  then 'SQL Server  2000  SP3+Q867878/867879/867880' 
 when '8.00.949'  then 'SQL Server  2000  SP3+Q843266' 
 when '8.00.948'  then 'SQL Server  2000  SP3+Q843263' 
 when '8.00.944'  then 'SQL Server  2000  SP3+Q839280' 
 when '8.00.937'  then 'SQL Server  2000  SP3+Q841776' 
 when '8.00.936'  then 'SQL Server  2000  SP3+Q841627' 
 when '8.00.935'  then 'SQL Server  2000  SP3+Q841401' 
 when '8.00.934'  then 'SQL Server  2000  SP3+Q841404' 
 when '8.00.933'  then 'SQL Server  2000  SP3+Q840856' 
 when '8.00.929'  then 'SQL Server  2000  SP3+Q839529' 
 when '8.00.928'  then 'SQL Server  2000  SP3+Q839589' 
 when '8.00.927'  then 'SQL Server  2000  SP3+Q839688' 
 when '8.00.926'  then 'SQL Server  2000  SP3+Q839523' 
 when '8.00.923'  then 'SQL Server  2000  SP3+Q838460' 
 when '8.00.922'  then 'SQL Server  2000  SP3+Q837970' 
 when '8.00.919'  then 'SQL Server  2000  SP3+Q837957' 
 when '8.00.916'  then 'SQL Server  2000  SP3+Q317989' 
 when '8.00.915'  then 'SQL Server  2000  SP3+Q837401' 
 when '8.00.913'  then 'SQL Server  2000  SP3+Q836651' 
 when '8.00.911'  then 'SQL Server  2000  SP3+Q837957' 
 when '8.00.910'  then 'SQL Server  2000  SP3+Q834798' 
 when '8.00.908'  then 'SQL Server  2000  SP3+Q834290' 
 when '8.00.904'  then 'SQL Server  2000  SP3+Q834453' 
 when '8.00.892'  then 'SQL Server  2000  SP3+Q833710' 
 when '8.00.891'  then 'SQL Server  2000  SP3+Q836141' 
 when '8.00.879'  then 'SQL Server  2000  SP3+Q832977' 
 when '8.00.878'  then 'SQL Server  2000  SP3+Q838166' 
 when '8.00.876'  then 'SQL Server  2000  SP3+Q830912/831997/831999' 
 when '8.00.873'  then 'SQL Server  2000  SP3+Q830887' 
 when '8.00.871'  then 'SQL Server  2000  SP3+Q830767/830860' 
 when '8.00.870'  then 'SQL Server  2000  SP3+Q830262' 
 when '8.00.869'  then 'SQL Server  2000  SP3+Q830588' 
 when '8.00.867'  then 'SQL Server  2000  SP3+Q830366' 
 when '8.00.866'  then 'SQL Server  2000  SP3+Q830366' 
 when '8.00.865'  then 'SQL Server  2000  SP3+Q830395/828945' 
 when '8.00.863'  then 'SQL Server  2000  SP3+Q829205/829444'
 when '8.00.859'  then 'SQL Server  2000  SP3+Q821334' 
 when '8.00.858'  then 'SQL Server  2000  SP3+Q828637' 
 when '8.00.857'  then 'SQL Server  2000  SP3+Q828017/827714/828308' 
 when '8.00.856'  then 'SQL Server  2000  SP3+Q828096' 
 when '8.00.854'  then 'SQL Server  2000  SP3+Q828699' 
 when '8.00.852'  then 'SQL Server  2000  SP3+Q830466/827954'
 when '8.00.851'  then 'SQL Server  2000  SP3+Q826754' 
 when '8.00.850'  then 'SQL Server  2000  SP3+Q826860/826815/826906' 
 when '8.00.848'  then 'SQL Server  2000  SP3+Q826822' 
 when '8.00.847'  then 'SQL Server  2000  SP3+Q826433' 
 when '8.00.845'  then 'SQL Server  2000  SP3+Q826364/825854' 
 when '8.00.844'  then 'SQL Server  2000  SP3+Q826080' 
 when '8.00.842'  then 'SQL Server  2000  SP3+Q825043' 
 when '8.00.841'  then 'SQL Server  2000  SP3+Q825225' 
 when '8.00.840'  then 'SQL Server  2000  SP3+Q319477/319477' 
 when '8.00.839'  then 'SQL Server  2000  SP3+Q823877/824027/820788' 
 when '8.00.837'  then 'SQL Server  2000  SP3+Q821741/548/740/823514' 
 when '8.00.819'  then 'SQL Server  2000  SP3+Q826161' 
 when '8.00.818'  then 'SQL Server  2000  SP3+Q821277/337/818388/826161/821280' 
 when '8.00.816'  then 'SQL Server  2000  SP3+Q818766' 
 when '8.00.814'  then 'SQL Server  2000  SP3+Q819662' 
 when '8.00.811'  then 'SQL Server  2000  SP3+Q819248/819662/818897' 
 when '8.00.807'  then 'SQL Server  2000  SP3+Q818899' 
 when '8.00.804'  then 'SQL Server  2000  SP3+Q818729' 
 when '8.00.801'  then 'SQL Server  2000  SP3+Q818540' 
 when '8.00.800'  then 'SQL Server  2000  SP3+Q818414/097/188' 
 when '8.00.798'  then 'SQL Server  2000  SP3+Q817464' 
 when '8.00.794'  then 'SQL Server  2000  SP3+Q817464/813524/816440/817709' 
 when '8.00.791'  then 'SQL Server  2000  SP3+Q815249' 
 when '8.00.790'  then 'SQL Server  2000  SP3+Q817081' 
 when '8.00.789'  then 'SQL Server  2000  SP3+Q816840' 
 when '8.00.788'  then 'SQL Server  2000  SP3+Q816985' 
 when '8.00.781'  then 'SQL Server  2000  SP3+Q815057' 
 when '8.00.780'  then 'SQL Server  2000  SP3+Q816084/810185' 
 when '8.00.779'  then 'SQL Server  2000  SP3+Q814035' 
 when '8.00.776'  then 'SQL Server  2000  SP3+Unidentified' 
 when '8.00.775'  then 'SQL Server  2000  SP3+Q815115' 
 when '8.00.769'  then 'SQL Server  2000  SP3+Q814889/93' 
 when '8.00.765'  then 'SQL Server  2000  SP3+Q810163/688/811611/813769/813759/812995/814665/460/813494' 
 when '8.00.763'  then 'SQL Server  2000  SP3+Q814113' 
 when '8.00.762'  then 'SQL Server  2000  SP3+Q814032' 
 when '8.00.760'  then 'SQL Server  2000  SP3/SP3a' 
 when '8.00.743'  then 'SQL Server  2000  SP3+Q818406/763' 
 when '8.00.741'  then 'SQL Server  2000  SP2+Q818096' 
 when '8.00.736'  then 'SQL Server  2000  SP2+Q816937' 
 when '8.00.735'  then 'SQL Server  2000  SP2+Q814889' 
 when '8.00.733'  then 'SQL Server  2000  SP2+Q813759' 
 when '8.00.730'  then 'SQL Server  2000  SP2+Q813769' 
 when '8.00.728'  then 'SQL Server  2000  SP2+Q814460' 
 when '8.00.725'  then 'SQL Server  2000  SP2+Q812995/813494' 
 when '8.00.723'  then 'SQL Server  2000  SP2+Q812798' 
 when '8.00.721'  then 'SQL Server  2000  SP2+Q812250/812393' 
 when '8.00.718'  then 'SQL Server  2000  SP2+Q811703' 
 when '8.00.715'  then 'SQL Server  2000  SP2+Q810688/811611' 
 when '8.00.713'  then 'SQL Server  2000  SP2/3+Q811205' 
 when '8.00.710'  then 'SQL Server  2000  SP2/3+Q811052' 
 when '8.00.705'  then 'SQL Server  2000  SP2+Q810920' 
 when '8.00.703'  then 'SQL Server  2000  SP2+Q810526' 
 when '8.00.702'  then 'SQL Server  2000 +Q328551' 
 when '8.00.701'  then 'SQL Server  2000  SP2+Q810026/810163' 
 when '8.00.700'  then 'SQL Server  2000  SP2+Q810072'
 when '8.00.696'  then 'SQL Server  2000  SP2+Q810052/10' 
 when '8.00.695'  then 'SQL Server  2000  SP2+Q331885/965/968' 
 when '8.00.693'  then 'SQL Server  2000  SP2+Q330212' 
 when '8.00.690'  then 'SQL Server  2000  SP2+Q311104' 
 when '8.00.686'  then 'SQL Server  2000  SP2+Q316333' 
 when '8.00.682'  then 'SQL Server  2000  SP3+Q319851' 
 when '8.00.679'  then 'SQL Server  2000  SP2+Q316333' 
 when '8.00.678'  then 'SQL Server  2000  SP3+Q328354' 
 when '8.00.667'  then 'SQL Server  2000  SP2+8/14 fix' 
 when '8.00.665'  then 'SQL Server  2000  SP2+8/8 fix' 
 when '8.00.661'  then 'SQL Server  2000  SP3+Q326999' 
 when '8.00.655'  then 'SQL Server  2000  SP2+7/24 fix' 
 when '8.00.652'  then 'SQL Server  2000  SP2+Q810010?' 
 when '8.00.650'  then 'SQL Server  2000  SP2+Q322853' 
 when '8.00.644'  then 'SQL Server  2000  SP3+Q324186'
 when '8.00.608'  then 'SQL Server  2000  SP2+Q319507' 
 when '8.00.604'  then 'SQL Server  2000  SP2+3/29 fix' 
 when '8.00.599'  then 'SQL Server  2000  SP2+Q319869' 
 when '8.00.594'  then 'SQL Server  2000  SP2+Q319477/319477' 
 when '8.00.578'  then 'SQL Server  2000  SP2+Q317979/318045' 
 when '8.00.561'  then 'SQL Server  2000  SP2+1/29 fix' 
 when '8.00.558'  then 'SQL Server  2000  SP2+Q314003/315395' 
 when '8.00.552'  then 'SQL Server  2000  SP2+Q313002/5' 
 when '8.00.534'  then 'SQL Server  2000  SP2.01' 
 when '8.00.532'  then 'SQL Server  2000  SP2' 
 when '8.00.475'  then 'SQL Server  2000  SP1+1/29 fix' 
 when '8.00.474'  then 'SQL Server  2000  SP1+Q315395' 
 when '8.00.473'  then 'SQL Server  2000  SP1+Q314003' 
 when '8.00.471'  then 'SQL Server  2000  SP1+Q313302' 
 when '8.00.469'  then 'SQL Server  2000  SP1+Q313005' 
 when '8.00.452'  then 'SQL Server  2000  SP1+Q308547' 
 when '8.00.444'  then 'SQL Server  2000  SP1+Q307540/307655' 
 when '8.00.443'  then 'SQL Server  2000  SP1+Q307538' 
 when '8.00.428'  then 'SQL Server  2000  SP1+Q304850' 
 when '8.00.384'  then 'SQL Server  2000  SP1' 
 when '8.00.296'  then 'SQL Server  2000  No SP+Q299717' 
 when '8.00.287'  then 'SQL Server  2000  No SP+Q297209' 
 when '8.00.251'  then 'SQL Server  2000  SP3+Q300194' 
 when '8.00.250'  then 'SQL Server  2000  No SP+Q291683' 
 when '8.00.249'  then 'SQL Server  2000  No SP+Q288122' 
 when '8.00.239'  then 'SQL Server  2000  No SP+Q285290' 
 when '8.00.233'  then 'SQL Server  2000  No SP+Q282416' 
 when '8.00.231'  then 'SQL Server  2000  No SP+Q282279' 
 when '8.00.226'  then 'SQL Server  2000  No SP+Q278239' 
 when '8.00.225'  then 'SQL Server  2000  No SP+Q281663' 
 when '8.00.223'  then 'SQL Server  2000  No SP+Q280380' 
 when '8.00.222'  then 'SQL Server  2000  No SP+Q281769' 
 when '8.00.218'  then 'SQL Server  2000  No SP+Q279183' 
 when '8.00.217'  then 'SQL Server  2000  No SP+Q279293/279296' 
 when '8.00.211'  then 'SQL Server  2000  No SP+Q276329' 
 when '8.00.210'  then 'SQL Server  2000  No SP+Q275900' 
 when '8.00.205'  then 'SQL Server  2000  No SP+Q274330' 
 when '8.00.204'  then 'SQL Server  2000  No SP+Q274329' 
 when '8.00.194'  then 'SQL Server  2000  RTM/No SP' 
 when '8.00.190'  then 'SQL Server  2000  Gold, no SP' 
 when '8.00.100'  then 'SQL Server  2000  Beta 2' 
 when '8.00.078'  then 'SQL Server  2000  EAP5' 
 when '8.00.047'  then 'SQL Server  2000  EAP4' 
 else 'Version not found. Update the script' 
end





update #tmp set sp=@sprealversion, servername=@@servername, servicename=@@servicename


select * from #tmp
drop table #tmp


end