Download SQL Patch Info

Question

Post reply

Download SQL Patch Info

Theo Ekelmans

SSCarpal Tunnel

Points: 4592
More actions
December 24, 2020 at 12:00 am

#343366

Comments posted to this topic are about the item Download SQL Patch Info

Viewing 15 posts - 46 through 60 (of 74 total)

You must be logged in to reply to this topic. Login to reply

Theo Ekelmans SSCarpal Tunnel Points: 4592 More actions · Answer 1

Ah...

It takes the collective experience of a lot of people to bring a good idea to life!

Alas the site will not allow me to edit the article, so I will somehow have to hare this and a few other recent comments in the chat!

As for the rocket science... not so much, it's not much more than a screenscraper script that spits out a table or two 😛

Thanks for sharing this, much appreciated !

Theo 🙂

This reply was modified 3 years, 10 months ago by Theo Ekelmans.

christianl Newbie Points: 3 More actions · Answer 2

Hello,

I can see that the “CU flag” for CU21 and CU22 is missing for SQL 2017, can you add them?

/Christian

Theo Ekelmans SSCarpal Tunnel Points: 4592 More actions · Answer 3

use [ServerInfo]
go

/******************************************************************************************
This script returns the info on http://sqlserverbuilds.blogspot.com as a table
*******************************************************************************************
Version:  1.0
 Author:  Theo Ekelmans
  Email:  theo@ekelmans.com
   Date:  2017-05-09
   
Version:  1.1
 Author:  Theo Ekelmans
   Date:  2019-01-09
 Change:  Small fixes due to some changes on sqlserverbuilds.blogspot.com

Version:  1.2
 Author:  Theo Ekelmans
   Date:  2019-003-13
 Change:  Small fixes due to some json artifacts   

Version:  1.3
 Author:  Onno Lagerwerf
  Email:  onno.lagerwerf@ordina.nl
   Date:  2020-08-07
 Change:   
- html h1 search entry changed in h2
- releasedate from html to date format
- mail pointing to different servers
- removed 'serverinfo' from objects
- extende Production version fixes
- alter major/minor.... calculated columns

Version:  1.4
 Author:  Theo Ekelmans
  Email:  theo@ekelmans.com
   Date:  2017-05-09
 Change:  Added some extra CU lines as we have passed CU21, which i did not expect :)

*******************************************************************************************
This script uses wget.exe from this excellent site: https://eternallybored.org/misc/wget/
Save the wget.exe file in the same folder as the ERRORLOG file (or change the path below)
******************************************************************************************/
set nocount on

-- Lots of royally sized vars :)
begin
declare @out table (
    ID int identity(1,1) PRIMARY KEY, -- All tables need an ID, right?
    LongSqlVersion varchar(1000), -- The header text of each SQL version
    SqlVersion varchar(1000), -- 7, 2000, 2005, 2008, 2008R2, 2012, 2016, 2017 etc.
    htmlID int, -- The order in which data is read from sqlserverbuilds.blogspot.com
    line varchar(8000),  -- The raw data from sqlserverbuilds.blogspot.com
    ProductVersion varchar(1000), -- This is the column to match the output of SERVERPROPERTY('ProductVersion') against, i have fixed all the exceptions i could find , thanks to MS for being so consistent .**NOT** :)
    build varchar(1000),  -- The build label that sqlserverbuilds.blogspot.com uses (this is not always the same as SERVERPROPERTY(ProductVersion'') reports), use this columnt together with the new columt to update your local table
    fileversion varchar(1000), -- Don't ask, i have no idea....
    kb varchar(1000),  -- The knowledgebase article for this patch
    description varchar(1000),  -- The knowledgebase description for this patch
    url varchar(1000), -- The knowledgebase download link for this patch
    releasedate varchar(1000), -- Guess....
    latest_sp bit, -- This bit is high for the latest SP for this SqlVersion (and the reason why i built this script)
    latest_cu bit, -- This bit is high for the latest CU for this SqlVersion (and the reason why i built this script)
    rtm bit, -- This bit is high is this build is the RTM (release to market) version, and most definitly not the build you want to be on!
    new bit) -- If you know what were the latest released builds? Say hello to yopur little bitty friend :)
declare @Cmd varchar(1000) 
declare @Path varchar(1000)
declare @FileName varchar (1024)
declare @OLEResult int
declare @FS int
declare @FileID int
declare @Message varchar (8000)
declare @LongSqlVersion as varchar(250)
declare @CurrentSqlVersion as varchar(20)
declare @ID int
declare @SqlVersion varchar(20)
declare @StartLine int
declare @EndLine int
declare @htmlID int
declare @line varchar(8000)
declare @ProductVersion varchar(1000)
declare @build varchar(1000)
declare @fileversion varchar(1000)
declare @KB varchar(1000)
declare @description varchar(1000)
declare @url varchar(1250)
declare @releasedate varchar(1000)
declare @latest_sp bit
declare @latest_cu bit
declare @rtm bit
declare @new bit
declare @pos int
declare @oldpos int
declare @counter int
declare @l varchar(1000) 
declare @htmlLine table(
[ID] [int] identity(1,1) NOT NULL, 
line varchar(max))
declare @SqlVersionTables table(
ID int identity(1,1) PRIMARY KEY,
LongSqlVersion varchar(250), 
SqlVersion varchar(20), 
StartLine int, 
EndLine int)
declare @html table(
[ID] [int] identity(1,1) NOT NULL, 
line varchar(max))
declare @htmlRows table (
ID int PRIMARY KEY, 
pos int, 
epos int, 
td varchar(8000))
end

-- Get SQL errorlog path (a convenient place to store WGET.EXE and the out.html files)
set @Path = (select substring(cast(SERVERPROPERTY('ErrorLogFileName') as varchar(256)), 1 , len(cast(SERVERPROPERTY('ErrorLogFileName') as varchar(256))) - 8))
set @FileName = @Path+'out.html'

-- Go get it (html output goes to the out.html)
set @Cmd = 'CMD /S /C " "'+@Path+'wget.exe" --quiet -O "'+@Path+'out.html" http://sqlserverbuilds.blogspot.nl " '
exec xp_cmdshell @Cmd, no_output 

-- Create an instance of the file system object
EXECUTE @OLEResult = sp_OACreate 'Scripting.FileSystemObject', @FS OUT
IF @OLEResult <> 0
BEGIN
PRINT 'Scripting.FileSystemObject'
PRINT 'Error code: ' + CONVERT (varchar, @OLEResult)
END

-- Open the out.htmlfile for reading
EXEC @OLEResult = sp_OAMethod @FS, 'OpenTextFile', @FileID OUT, @FileName, 1, 1
IF @OLEResult <> 0
BEGIN
PRINT 'OpenTextFile'
PRINT 'Error code: ' + CONVERT (varchar, @OLEResult)
END

-- Read the first line into the @Message variable
EXECUTE @OLEResult = sp_OAMethod @FileID, 'ReadLine', @Message OUT

-- Keep looping through the file until the @OLEResult variable is < 0; this indicates that the end of the file has been reached.
WHILE @OLEResult >= 0
BEGIN
-- Save each line into a table variable
insert into @html(line)
select @Message

EXECUTE @OLEResult = sp_OAMethod @FileID, 'ReadLine', @Message OUT
END

-- Clean up
EXECUTE @OLEResult = sp_OADestroy @FileID
EXECUTE @OLEResult = sp_OADestroy @FS

----Debug point------
-- select * from @html 

---------------------------------------------------------------------
-- Check at what line each of the SQL version tables start and end
---------------------------------------------------------------------
insert into @SqlVersionTables 
selectSUBSTRING(line, CHARINDEX('>', line, 8) + 1, len(line) - 6 - CHARINDEX('>', line, 8) + 1) as [LongSqlVersion],
SUBSTRING(line, 11, CHARINDEX('>', line, 8) -11) as [SqlVersion], 
ID, 
0 
from@html 
whereline like '%<h2 id=sql%'

update @SqlVersionTables
set EndLine = ( select top 1 ID
from @html 
where ID > s.StartLine
and line like '</table>%'
order by ID) 
from @SqlVersionTables s

----Debug point------
-- select * from @SqlVersionTables 
    
------------------------------------------------------------------------
-- Extract html lines for each of the sql version tables 
------------------------------------------------------------------------
declarecurSqlVer CURSOR FOR  
select ID
,LongSqlVersion
,SqlVersion
,StartLine
,EndLine
from@SqlVersionTables 

OPEN curSqlVer   
FETCH NEXT FROM curSqlVer INTO @ID, @LongSqlVersion, @CurrentSqlVersion, @StartLine, @EndLine 

WHILE @@FETCH_STATUS = 0   
BEGIN   

    insert into @out 
    select  @LongSqlVersion 
,@CurrentSqlVersion 
,ID
,replace(replace(line, '</tr>', ''), '<tr>', '') as line -- strip the tr tags
,'' as ProductVersion
,'' as build
,'' as fv
,'' as kb
,'' as descr
,'' as url 
,getdate() as rd
,0 as lsp 
,0 as lcu
,0 as rtm 
,0 as new
    from   @html
    where   ID between (select StartLine from @SqlVersionTables where SqlVersion = @CurrentSqlVersion ) + 1
       and (select EndLine   from @SqlVersionTables where SqlVersion = @CurrentSqlVersion ) 
    and   line like '<tr><td%' -- Only the rows of the tables are interesting
    and   len(line) > 0        -- IF they are filled 
    order by ID

    FETCH NEXT FROM curSqlVer INTO @ID, @LongSqlVersion, @CurrentSqlVersion, @StartLine, @EndLine    
END   

CLOSE curSqlVer   
DEALLOCATE curSqlVer

---------------------------------------------
-- Loop thought the table rows, stip all html tags 
---------------------------------------------

declare curOut CURSOR FOR 
select  ID, 
   LongSqlVersion, 
   SqlVersion, 
   htmlID, 
   line, 
   ProductVersion,
   build, 
   fileversion, 
   kb,
   description, 
   url, 
   releasedate, 
   latest_sp, 
   latest_cu, 
   rtm,
   new
FROM   @out
ORDER BY htmlID
FOR UPDATE

OPEN curOut   
FETCH NEXT FROM curOut INTO @ID, @LongSqlVersion, @SqlVersion, @htmlID, @line, @ProductVersion, @build, @fileversion, @KB, @description, @url, @releasedate, @latest_sp, @latest_cu, @rtm, @new 

WHILE @@FETCH_STATUS = 0   
BEGIN   
    set @counter = 1 
    set @oldpos=0
    set @pos=patindex('%<td%',@line) 

-- Loop through the chars in the html row and find the start of every td tag
-- Insert every td into a row in @htmlrows
    while @pos > 0 and @oldpos<>@pos
    begin
   insert into @htmlRows Values (@counter, @pos, 0, '')

   set @oldpos=@pos
   set @pos=patindex('%<td%',Substring(@line,@pos + 1,len(@line))) + @pos
   
   update @htmlRows 
   set  epos = case when @oldpos=@pos then len(@line) else @pos -1 end 
  ,td = substring(@line, @oldpos, case when (@pos -1 - @oldpos) < 0 then len(@line) else @pos - @oldpos end) 
   where pos = @oldpos

   set @counter = @counter + 1
    end

---------------------------------------------------------------------
-- Decode and cleanup the td htmlrows 
---------------------------------------------------------------------

    -- ID Correction for sql7, because it has no File version column, all ID's need to shift one place
    if @SqlVersion = '7' update @htmlRows set ID = ID + 1 where ID > 3

    -- Check for intersting flags (Latest SP & CU, RTM and New flags)
    if exists (select td from @htmlRows where td like '%Latest&nbsp;CU%')   insert into @htmlRows Values ((select max(ID) from @htmlRows) + 1, 10000, 0, 'Latest CU')
if exists (select td from @htmlRows where td like '%Latest&nbsp;SP%')   insert into @htmlRows Values ((select max(ID) from @htmlRows) + 1, 20000, 0, 'Latest SP')
if exists (select td from @htmlRows where td like '%<td class=rtm>%')   insert into @htmlRows Values ((select max(ID) from @htmlRows) + 1, 40000, 0, 'RTM')
if exists (select td from @htmlRows where td like '%*new%')insert into @htmlRows Values ((select max(ID) from @htmlRows) + 1, 30000, 0, '*new') 

    -- remove the unneeded html tag and class crap
    update @htmlRows set td = replace(td, '<td class=sp>', '')
    update @htmlRows set td = replace(td, '<td class=cu>', '')
    update @htmlRows set td = replace(td, '<td class=h>', '')
    update @htmlRows set td = replace(td, '<td>', '')
    update @htmlRows set td = replace(td, '</td>', '')
    update @htmlRows set td = replace(td, '<td class=rtm>', '')
    update @htmlRows set td = replace(td, '', '')
    update @htmlRows set td = replace(td, '', '')
    update @htmlRows set td = replace(td, '&nbsp; Latest&nbsp;SP', '')
    update @htmlRows set td = replace(td, '&nbsp; Latest&nbsp;CU', '')
    update @htmlRows set td = replace(td, '<font color="#FF0000" size="1"> *new</font>', '')
    update @htmlRows set td = replace(td, '&quot;', '"') -- JSON does *not* like &quot;


update @htmlRows set td = replace(td, '', '-') -- JSON does *not* like span;
update @htmlRows set td = replace(td, '', '-') -- JSON does *not* like span;


    update @htmlRows set td = replace(td, '<time datetime="', '') -- DT prefix;

----Debug point------
    --select @line
    --select * from @htmlRows

--------------------------------------------------------------------------------
-- Extract the build, fileversion, KB, url etc and place them in their columns 
--------------------------------------------------------------------------------

-- Build
    UPDATE @out SET build = (select td from @htmlRows where ID = 1) WHERE CURRENT OF curOut

    -- ProductVersion
    UPDATE @out SET ProductVersion = (select td from @htmlRows where ID = 2) WHERE CURRENT OF curOut -- I reused the hidden column on the website for ProductVersion matching (after corrections... lot's of em....)

--Fileversion
    if @SqlVersion <> '7' 
    UPDATE @out SET fileversion = (select td from @htmlRows where ID = 3) WHERE CURRENT OF curOut  

    UPDATE @out SET kb = (select td from @htmlRows where ID = 5) WHERE CURRENT OF curOut  

    set @l = (select td from @htmlRows where ID = 6)
    
-- Description and url (if any)
    if left(@l, 8) = '<a href='
    begin
   UPDATE @out SET description = (select substring(@l, charindex('>', @l) +1 , charindex('<', @l, charindex('>', @l)) - charindex('>', @l) -1)) WHERE CURRENT OF curOut  
   UPDATE @out SET url = substring(@l, charindex('"', @l) + 1, charindex('"', @l, charindex('"', @l)+1) - charindex('"', @l) - 1) WHERE CURRENT OF curOut  
    end
    else
    begin
   UPDATE @out SET description = @l WHERE CURRENT OF curOut  
   UPDATE @out SET url = '' WHERE CURRENT OF curOut  
    end
    
-- And the rest....
    --UPDATE @out SET releasedate = (select td from @htmlRows where ID = 7) WHERE CURRENT OF curOut 
UPDATE @out SET releasedate = (select reverse(substring(reverse(td), 8, 10)) from @htmlRows where ID = 7) WHERE CURRENT OF curOut 
    UPDATE @out SET latest_sp = case when exists (select td from @htmlRows where pos = 20000) then 1 else 0 end WHERE CURRENT OF curOut
    UPDATE @out SET latest_cu = case when exists (select td from @htmlRows where pos = 10000) then 1 else 0 end WHERE CURRENT OF curOut
    UPDATE @out SET rtm = case when exists (select td from @htmlRows where pos = 40000) then 1 else 0 end WHERE CURRENT OF curOut
    UPDATE @out SET new  = case when exists (select td from @htmlRows where pos = 30000) then 1 else 0 end WHERE CURRENT OF curOut
    
-- Prepare for the next loop
delete from @htmlRows 

    FETCH NEXT FROM curOut INTO @ID, @LongSqlVersion, @SqlVersion, @htmlID, @line, @ProductVersion, @build, @fileversion, @KB, @description, @url, @releasedate, @latest_sp, @latest_cu, @rtm, @new 
END   

CLOSE curOut   
DEALLOCATE curOut

---------------------------------------------------------------------------------------------------------------------------------------------------
-- ProductVersion fixes, so you can use SERVERPROPERTY('ProductVersion') to match your SQL instance build to sqlserverbuilds.blogspot.com
---------------------------------------------------------------------------------------------------------------------------------------------------
----2019--
--set @ID = (select ID from @out where ProductVersion = '15.00.1200.?')
--Update @out SET ProductVersion = '15.00.1200.0' where ID = @ID -- the minor version does not support a ?
--Update @out SET fileversion = '2018.150.1200.0' where ID = @ID -- the file version does not support a ?
--Update @out SET build = '15.0.1200.0' where ID = @ID -- the build does not support a ?

--delete FROM [ServerInfo].[dbo].[tblBuildList] where ReleaseDate = '2018-12-11' -- fix after the fact 

--2019--
Update @out SET ProductVersion = build where SqlVersion = '2019' -- the minor version does not report a leading 0

--2017--
Update @out SET ProductVersion = build where SqlVersion = '2017' -- the minor version does not report a leading 0

--2016--
Update @out SET ProductVersion = build where SqlVersion = '2016' -- the minor version does not report a leading 0

--2014--
set @ID = (select ID from @out where ProductVersion = '12.0.5537 or 12.0.5538')
insert into @out select LongSqlVersion, SqlVersion, htmlID, line, '12.00.5538' ,build, fileversion, kb,description, url, releasedate, latest_sp, latest_cu, rtm, new FROM @out where ID = @ID
Update @out SET ProductVersion = '12.00.5537' where ID = @ID
Update @out SET ProductVersion = ProductVersion + '.0' where SqlVersion = '2014' and build <> '11.00.9120' -- attached .0 for consistent matching with using SERVERPROPERTY('ProductVersion') 
set @ID = (select ID from @out where ProductVersion = '12.0.4100.0') --2014 RTM SP1 can have a release value of 1
Update @out SET ProductVersion = '12.0.4100.1' where ID = @ID

--2012--
delete from @out where fileversion = '2011.110.9000.5' -- Extremely rare version with a non-standard build the makes sorting a pain.... i vote to drop it :)
Update @out SET ProductVersion = ProductVersion + '.0' where SqlVersion = '2012' or build = '11.00.9120' -- attached .0 for consistent matching with using SERVERPROPERTY('ProductVersion') 

--2008r2--
Update @out SET ProductVersion = build + '.0' where SqlVersion = '2008r2' -- r2 was missing in the hidden column on the website, attached .0 for consistent matching with using SERVERPROPERTY('ProductVersion') 
set @ID = (select ID from @out where ProductVersion = '10.50.6000.0') --r2 SP3 can have a release of 34
Update @out SET ProductVersion = '10.50.6000.34' where ID = @ID
set @ID = (select ID from @out where ProductVersion = '10.50.1600.0') --r2 RTM SP1 can have a release of 1
Update @out SET ProductVersion = '10.50.1600.1' where ID = @ID

--2008--
Update @out SET ProductVersion = ProductVersion + '.0' where SqlVersion = '2008' -- the minor version does not report a leading 0, attached .0 for consistent matching with using SERVERPROPERTY('ProductVersion') 

--2005--
Update @out SET ProductVersion = build + '.00' where SqlVersion = '2005' -- attached .0 for consistent matching with using SERVERPROPERTY('ProductVersion') 
Update @out SET latest_cu = 1 where fileversion = '2005.90.5266.0' -- attached .0 for consistent matching with using SERVERPROPERTY('ProductVersion') 

--2000--
Update @out SET ProductVersion = build + '.0' where SqlVersion = '2000' -- attached .0 for consistent matching with using SERVERPROPERTY('ProductVersion') 

--7--
Update @out SET ProductVersion = build + '.0' where SqlVersion = '7' -- attached .0 for consistent matching with using SERVERPROPERTY('ProductVersion') 


--------------------------------------------------------------------------------
-- Step 2 : save @out into tblBuildListImport and update tblBuildList 
--------------------------------------------------------------------------------
IF OBJECT_ID('dbo.tblBuildListImport', 'U') IS NOT NULL DROP TABLE dbo.tblBuildListImport; 

-- Used for the alert email
declare @mailbody varchar(1000) = 'See https://as-ordsql001.azurewebsites.net/SqlBuilds2.aspx for details' 
    + char(13) + char(10) + 
    'and https://as-ordsql001.azurewebsites.net/Reports/report/Server%20info/Customer%20SQL%20Version for customer impact'
-- OLA, 07-08-2020, webadres naar Reports werkt nog niet!

--------------------------------------------------------------------------------
-- Extraction and corrections complete, save into the tblBuildListImport
--------------------------------------------------------------------------------
select  *
INTO [dbo].[tblBuildListImport]
FROM@out
ORDER BY htmlID

--------------------------------------------------------------------------------
-- add computed coumns for easy sorting
--------------------------------------------------------------------------------
ALTER TABLE dbo.tblBuildListImport ADD
    [Major] AS (convert(int, reverse(parsename(reverse([build]), 1)))),
[Minor] AS (convert(int, reverse(parsename(reverse([build]), 2)))),
[BuildNr] AS (convert(int, reverse(parsename(reverse([build]), 3)))),
[Revision] AS (isnull(convert(int, reverse(parsename(reverse([build]), 4))), 0))

--------------------------------------------------------------------------------
-- Save new build records in tblBuildList
--------------------------------------------------------------------------------
INSERT INTO [dbo].[tblBuildList]
           ([Version]
 ,l.[ProductVersion]
           ,[Build]
           ,[FileVersion]
           ,[KBDescription]
 ,l.
           ,[ReleaseDate]
           ,[SP]
           ,[CU]
           ,[HF]
           ,[RTM]
           ,[CTP]
 ,[LatestSP]
 ,[LatestCU]
 ,[New]
           ,[Comment])
SELECT upper([SqlVersion])
 ,i.[ProductVersion]
      ,[build]
      ,[fileversion]
      ,[description]
 ,i.
      ,[releasedate]
 ,0 as SP
 ,0 as CU
 ,0 as HF
 ,[rtm]
 ,0 as CTP
 ,latest_sp
 ,latest_cu
 ,new
 ,' auto add' as Cmt
FROM[dbo].[tblBuildListImport] i
left join dbo.tblBuildList l on i.build = l.Build 
where l.ID is null
ORDER BY htmlID 


--------------------------------------------------------------------------------
-- Send email if new records are found
--------------------------------------------------------------------------------
if @@ROWCOUNT > 0
execute msdb.dbo.sp_send_dbmail
 @profile_name = 'Ordina'
,@recipients = 'mssql@ordina.nl'
,@subject = 'New patches on http://sqlserverbuilds.blogspot.com detected'
,@body = @mailbody
,@body_format = 'HTML' -- or TEXT
,@importance = 'Normal' --Low Normal High
,@sensitivity = 'Normal' --Normal Personal Private Confidential


--------------------------------------------------------------------------------
-- Update info (Because it could change)
--------------------------------------------------------------------------------
update[dbo].[tblBuildList]
set [ProductVersion] = i.[ProductVersion]
,[Version] = UPPER([Version])
, = i.
,[LatestSP] = i.[latest_sp]
,[LatestCU] = i.[latest_cu]
,[New] = i.[new]
FROM[dbo].[tblBuildListImport] i
left join [dbo].[tblBuildList] l 
on i.build = l.Build 

--------------------------------------------------------------------------------
-- Update flags SP, CU, HF and CTP flags (extra search fields)
--------------------------------------------------------------------------------
update  dbo.tblBuildList
set   SP = 1 
where  UPPER(KBDescription) like UPPER('%Service Pack 17 (SP17)')
or   UPPER(KBDescription) like UPPER('%Service Pack 16 (SP16)')
or   UPPER(KBDescription) like UPPER('%Service Pack 15 (SP15)')
or   UPPER(KBDescription) like UPPER('%Service Pack 14 (SP14)')
or   UPPER(KBDescription) like UPPER('%Service Pack 13 (SP13)')
or   UPPER(KBDescription) like UPPER('%Service Pack 12 (SP12)')
or   UPPER(KBDescription) like UPPER('%Service Pack 11 (SP11)')
or   UPPER(KBDescription) like UPPER('%Service Pack 10 (SP10)')
or   UPPER(KBDescription) like UPPER('%Service Pack 9 (SP9)')
or   UPPER(KBDescription) like UPPER('%Service Pack 8 (SP7)')
or   UPPER(KBDescription) like UPPER('%Service Pack 7 (SP7)')
or   UPPER(KBDescription) like UPPER('%Service Pack 6 (SP6)')
or   UPPER(KBDescription) like UPPER('%Service Pack 5 (SP5)')
or   UPPER(KBDescription) like UPPER('%Service Pack 4 (SP4)')
or   UPPER(KBDescription) like UPPER('%Service Pack 3 (SP3)')
or   UPPER(KBDescription) like UPPER('%Service Pack 2 (SP2)')
or   UPPER(KBDescription) like UPPER('%Service Pack 1 (SP1)')

update  dbo.tblBuildList
set   CU = 1 
where  UPPER(KBDescription) like UPPER('%Cumulative update 33 (CU33)%')
or     UPPER(KBDescription) like UPPER('%Cumulative update 32 (CU32)%')
or     UPPER(KBDescription) like UPPER('%Cumulative update 31 (CU31)%')
or     UPPER(KBDescription) like UPPER('%Cumulative update 30 (CU30)%')
or     UPPER(KBDescription) like UPPER('%Cumulative update 29 (CU29)%')
or     UPPER(KBDescription) like UPPER('%Cumulative update 28 (CU28)%')
or     UPPER(KBDescription) like UPPER('%Cumulative update 27 (CU27)%')
or     UPPER(KBDescription) like UPPER('%Cumulative update 26 (CU26)%')
or     UPPER(KBDescription) like UPPER('%Cumulative update 25 (CU25)%')
or     UPPER(KBDescription) like UPPER('%Cumulative update 24 (CU24)%')
or     UPPER(KBDescription) like UPPER('%Cumulative update 23 (CU23)%')
or     UPPER(KBDescription) like UPPER('%Cumulative update 22 (CU22)%')
or     UPPER(KBDescription) like UPPER('%Cumulative update 21 (CU21)%')
or     UPPER(KBDescription) like UPPER('%Cumulative update 20 (CU20)%')
or   UPPER(KBDescription) like UPPER('%Cumulative update 19 (CU19)%')
or   UPPER(KBDescription) like UPPER('%Cumulative update 18 (CU18)%')
or   UPPER(KBDescription) like UPPER('%Cumulative update 17 (CU17)%')
or   UPPER(KBDescription) like UPPER('%Cumulative update 16 (CU16)%')
or   UPPER(KBDescription) like UPPER('%Cumulative update 15 (CU15)%')
or   UPPER(KBDescription) like UPPER('%Cumulative update 14 (CU14)%')
or   UPPER(KBDescription) like UPPER('%Cumulative update 13 (CU13)%')
or   UPPER(KBDescription) like UPPER('%Cumulative update 12 (CU12)%')
or   UPPER(KBDescription) like UPPER('%Cumulative update 11 (CU11)%')
or   UPPER(KBDescription) like UPPER('%Cumulative update 10 (CU10)%')
or   UPPER(KBDescription) like UPPER('%Cumulative update 9 (CU9)%')
or   UPPER(KBDescription) like UPPER('%Cumulative update 8 (CU8)%')
or   UPPER(KBDescription) like UPPER('%Cumulative update 7 (CU7)%')
or   UPPER(KBDescription) like UPPER('%Cumulative update 6 (CU6)%')
or   UPPER(KBDescription) like UPPER('%Cumulative update 5 (CU5)%')
or   UPPER(KBDescription) like UPPER('%Cumulative update 4 (CU4)%')
or   UPPER(KBDescription) like UPPER('%Cumulative update 3 (CU3)%')
or   UPPER(KBDescription) like UPPER('%Cumulative update 2 (CU2)%')
or   UPPER(KBDescription) like UPPER('%Cumulative update 1 (CU1)%')

update  dbo.tblBuildList
set   CU = 1 
where  UPPER(KBDescription) like UPPER('%Cumulative update package 33 (CU33)%')
or   UPPER(KBDescription) like UPPER('%Cumulative update package 32 (CU32)%')
or   UPPER(KBDescription) like UPPER('%Cumulative update package 31 (CU31)%')
or   UPPER(KBDescription) like UPPER('%Cumulative update package 30 (CU30)%')
or   UPPER(KBDescription) like UPPER('%Cumulative update package 29 (CU29)%')
or   UPPER(KBDescription) like UPPER('%Cumulative update package 28 (CU28)%')
or   UPPER(KBDescription) like UPPER('%Cumulative update package 27 (CU27)%')
or   UPPER(KBDescription) like UPPER('%Cumulative update package 26 (CU26)%')
or   UPPER(KBDescription) like UPPER('%Cumulative update package 25 (CU25)%')
or   UPPER(KBDescription) like UPPER('%Cumulative update package 24 (CU24)%')
or   UPPER(KBDescription) like UPPER('%Cumulative update package 23 (CU23)%')
or   UPPER(KBDescription) like UPPER('%Cumulative update package 22 (CU22)%')
or   UPPER(KBDescription) like UPPER('%Cumulative update package 21 (CU21)%')
or   UPPER(KBDescription) like UPPER('%Cumulative update package 20 (CU20)%')
or   UPPER(KBDescription) like UPPER('%Cumulative update package 19 (CU19)%')
or   UPPER(KBDescription) like UPPER('%Cumulative update package 18 (CU18)%')
or   UPPER(KBDescription) like UPPER('%Cumulative update package 17 (CU17)%')
or   UPPER(KBDescription) like UPPER('%Cumulative update package 16 (CU16)%')
or   UPPER(KBDescription) like UPPER('%Cumulative update package 15 (CU15)%')
or   UPPER(KBDescription) like UPPER('%Cumulative update package 14 (CU14)%')
or   UPPER(KBDescription) like UPPER('%Cumulative update package 13 (CU13)%')
or   UPPER(KBDescription) like UPPER('%Cumulative update package 12 (CU12)%')
or   UPPER(KBDescription) like UPPER('%Cumulative update package 11 (CU11)%')
or   UPPER(KBDescription) like UPPER('%Cumulative update package 10 (CU10)%')
or   UPPER(KBDescription) like UPPER('%Cumulative update package 9 (CU9)%')
or   UPPER(KBDescription) like UPPER('%Cumulative update package 8 (CU8)%')
or   UPPER(KBDescription) like UPPER('%Cumulative update package 7 (CU7)%')
or   UPPER(KBDescription) like UPPER('%Cumulative update package 6 (CU6)%')
or   UPPER(KBDescription) like UPPER('%Cumulative update package 5 (CU5)%')
or   UPPER(KBDescription) like UPPER('%Cumulative update package 4 (CU4)%')
or   UPPER(KBDescription) like UPPER('%Cumulative update package 3 (CU3)%')
or   UPPER(KBDescription) like UPPER('%Cumulative update package 2 (CU2)%')
or   UPPER(KBDescription) like UPPER('%Cumulative update package 1 (CU1)%')

update  dbo.tblBuildList
set   HF = 1 
where   UPPER(KBDescription) like UPPER('%Hotfix%')

update  dbo.tblBuildList
set   CTP = 1 
where   UPPER(KBDescription) like UPPER('%Community Technology Preview%')

update  dbo.tblBuildList
set   CTP = 1 
where   UPPER(KBDescription) like UPPER('%Release Candidate%')

--------------------------------------------------------------------------------
-- Done
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
-- Time to select what you need, or use the table for automated patch checks
--------------------------------------------------------------------------------

---- I.E. The latest SP and CU per SQL version
--select  *
--from   dbo.tblBuildList
--where   LatestSP = 1 
--or    LatestCU = 1
--ORDER BY Major desc
--   ,Minor desc
--   ,BuildNr desc
--   ,Revision desc?

use [ServerInfo]
go

/******************************************************************************************
This script returns the info on http://sqlserverbuilds.blogspot.com as a table
*******************************************************************************************
Version:  1.0
 Author:  Theo Ekelmans
  Email:  theo@ekelmans.com
   Date:  2017-05-09

Version:  1.1
 Author:  Onno Lagerwerf
  Email:  onno.lagerwerf@ordina.nl
   Date:  2020-08-07
- html h1 search entry changed in h2
- releasedate from html to date format
- mail pointing to different servers
- removed 'serverinfo' from objects
- extende Production version fixes
- alter major/minor.... calculated columns

Version:  1.2
 Author:  Theo Ekelmans
  Email:  theo@ekelmans.com
   Date:  2020-09-30
- Added some extra CU lines as we have passed CU21, which i did not expect :)
*******************************************************************************************
This script uses wget.exe from this excellent site: https://eternallybored.org/misc/wget/
Save the wget.exe file in the same folder as the ERRORLOG file (or change the path below)
******************************************************************************************/
set nocount on

-- Lots of royally sized vars :)
begin
declare @out table (
    ID int identity(1,1) PRIMARY KEY, -- All tables need an ID, right?
    LongSqlVersion varchar(1000), -- The header text of each SQL version
    SqlVersion varchar(1000), -- 7, 2000, 2005, 2008, 2008R2, 2012, 2016, 2017 etc.
    htmlID int, -- The order in which data is read from sqlserverbuilds.blogspot.com
    line varchar(8000),  -- The raw data from sqlserverbuilds.blogspot.com
    ProductVersion varchar(1000), -- This is the column to match the output of SERVERPROPERTY('ProductVersion') against, i have fixed all the exceptions i could find , thanks to MS for being so consistent .**NOT** :)
    build varchar(1000),  -- The build label that sqlserverbuilds.blogspot.com uses (this is not always the same as SERVERPROPERTY(ProductVersion'') reports), use this columnt together with the new columt to update your local table
    fileversion varchar(1000), -- Don't ask, i have no idea....
    kb varchar(1000),  -- The knowledgebase article for this patch
    description varchar(1000),  -- The knowledgebase description for this patch
    url varchar(1000), -- The knowledgebase download link for this patch
    releasedate varchar(1000), -- Guess....
    latest_sp bit, -- This bit is high for the latest SP for this SqlVersion (and the reason why i built this script)
    latest_cu bit, -- This bit is high for the latest CU for this SqlVersion (and the reason why i built this script)
    rtm bit, -- This bit is high is this build is the RTM (release to market) version, and most definitly not the build you want to be on!
    new bit) -- If you know what were the latest released builds? Say hello to yopur little bitty friend :)
declare @Cmd varchar(1000) 
declare @Path varchar(1000)
declare @FileName varchar (1024)
declare @OLEResult int
declare @FS int
declare @FileID int
declare @Message varchar (8000)
declare @LongSqlVersion as varchar(250)
declare @CurrentSqlVersion as varchar(20)
declare @ID int
declare @SqlVersion varchar(20)
declare @StartLine int
declare @EndLine int
declare @htmlID int
declare @line varchar(8000)
declare @ProductVersion varchar(1000)
declare @build varchar(1000)
declare @fileversion varchar(1000)
declare @KB varchar(1000)
declare @description varchar(1000)
declare @url varchar(1250)
declare @releasedate varchar(1000)
declare @latest_sp bit
declare @latest_cu bit
declare @rtm bit
declare @new bit
declare @pos int
declare @oldpos int
declare @counter int
declare @l varchar(1000) 
declare @htmlLine table(
[ID] [int] identity(1,1) NOT NULL, 
line varchar(max))
declare @SqlVersionTables table(
ID int identity(1,1) PRIMARY KEY,
LongSqlVersion varchar(250), 
SqlVersion varchar(20), 
StartLine int, 
EndLine int)
declare @html table(
[ID] [int] identity(1,1) NOT NULL, 
line varchar(max))
declare @htmlRows table (
ID int PRIMARY KEY, 
pos int, 
epos int, 
td varchar(8000))
end

-- Get SQL errorlog path (a convenient place to store WGET.EXE and the out.html files)
set @Path = (select substring(cast(SERVERPROPERTY('ErrorLogFileName') as varchar(256)), 1 , len(cast(SERVERPROPERTY('ErrorLogFileName') as varchar(256))) - 8))
set @FileName = @Path+'out.html'

-- Go get it (html output goes to the out.html)
set @Cmd = 'CMD /S /C " "'+@Path+'wget.exe" --quiet -O "'+@Path+'out.html" http://sqlserverbuilds.blogspot.nl " '
exec xp_cmdshell @Cmd, no_output 

-- Create an instance of the file system object
EXECUTE @OLEResult = sp_OACreate 'Scripting.FileSystemObject', @FS OUT
IF @OLEResult <> 0
BEGIN
PRINT 'Scripting.FileSystemObject'
PRINT 'Error code: ' + CONVERT (varchar, @OLEResult)
END

-- Open the out.htmlfile for reading
EXEC @OLEResult = sp_OAMethod @FS, 'OpenTextFile', @FileID OUT, @FileName, 1, 1
IF @OLEResult <> 0
BEGIN
PRINT 'OpenTextFile'
PRINT 'Error code: ' + CONVERT (varchar, @OLEResult)
END

-- Read the first line into the @Message variable
EXECUTE @OLEResult = sp_OAMethod @FileID, 'ReadLine', @Message OUT

-- Keep looping through the file until the @OLEResult variable is < 0; this indicates that the end of the file has been reached.
WHILE @OLEResult >= 0
BEGIN
-- Save each line into a table variable
insert into @html(line)
select @Message

EXECUTE @OLEResult = sp_OAMethod @FileID, 'ReadLine', @Message OUT
END

-- Clean up
EXECUTE @OLEResult = sp_OADestroy @FileID
EXECUTE @OLEResult = sp_OADestroy @FS

----Debug point------
-- select * from @html 

---------------------------------------------------------------------
-- Check at what line each of the SQL version tables start and end
---------------------------------------------------------------------
insert into @SqlVersionTables 
selectSUBSTRING(line, CHARINDEX('>', line, 8) + 1, len(line) - 6 - CHARINDEX('>', line, 8) + 1) as [LongSqlVersion],
SUBSTRING(line, 11, CHARINDEX('>', line, 8) -11) as [SqlVersion], 
ID, 
0 
from@html 
whereline like '%<h2 id=sql%'

update @SqlVersionTables
set EndLine = ( select top 1 ID
from @html 
where ID > s.StartLine
and line like '</table>%'
order by ID) 
from @SqlVersionTables s

----Debug point------
-- select * from @SqlVersionTables 
    
------------------------------------------------------------------------
-- Extract html lines for each of the sql version tables 
------------------------------------------------------------------------
declarecurSqlVer CURSOR FOR  
select ID
,LongSqlVersion
,SqlVersion
,StartLine
,EndLine
from@SqlVersionTables 

OPEN curSqlVer   
FETCH NEXT FROM curSqlVer INTO @ID, @LongSqlVersion, @CurrentSqlVersion, @StartLine, @EndLine 

WHILE @@FETCH_STATUS = 0   
BEGIN   

    insert into @out 
    select  @LongSqlVersion 
,@CurrentSqlVersion 
,ID
,replace(replace(line, '</tr>', ''), '<tr>', '') as line -- strip the tr tags
,'' as ProductVersion
,'' as build
,'' as fv
,'' as kb
,'' as descr
,'' as url 
,getdate() as rd
,0 as lsp 
,0 as lcu
,0 as rtm 
,0 as new
    from   @html
    where   ID between (select StartLine from @SqlVersionTables where SqlVersion = @CurrentSqlVersion ) + 1
       and (select EndLine   from @SqlVersionTables where SqlVersion = @CurrentSqlVersion ) 
    and   line like '<tr><td%' -- Only the rows of the tables are interesting
    and   len(line) > 0        -- IF they are filled 
    order by ID

    FETCH NEXT FROM curSqlVer INTO @ID, @LongSqlVersion, @CurrentSqlVersion, @StartLine, @EndLine    
END   

CLOSE curSqlVer   
DEALLOCATE curSqlVer

---------------------------------------------
-- Loop thought the table rows, stip all html tags 
---------------------------------------------

declare curOut CURSOR FOR 
select  ID, 
   LongSqlVersion, 
   SqlVersion, 
   htmlID, 
   line, 
   ProductVersion,
   build, 
   fileversion, 
   kb,
   description, 
   url, 
   releasedate, 
   latest_sp, 
   latest_cu, 
   rtm,
   new
FROM   @out
ORDER BY htmlID
FOR UPDATE

OPEN curOut   
FETCH NEXT FROM curOut INTO @ID, @LongSqlVersion, @SqlVersion, @htmlID, @line, @ProductVersion, @build, @fileversion, @KB, @description, @url, @releasedate, @latest_sp, @latest_cu, @rtm, @new 

WHILE @@FETCH_STATUS = 0   
BEGIN   
    set @counter = 1 
    set @oldpos=0
    set @pos=patindex('%<td%',@line) 

-- Loop through the chars in the html row and find the start of every td tag
-- Insert every td into a row in @htmlrows
    while @pos > 0 and @oldpos<>@pos
    begin
   insert into @htmlRows Values (@counter, @pos, 0, '')

   set @oldpos=@pos
   set @pos=patindex('%<td%',Substring(@line,@pos + 1,len(@line))) + @pos
   
   update @htmlRows 
   set  epos = case when @oldpos=@pos then len(@line) else @pos -1 end 
  ,td = substring(@line, @oldpos, case when (@pos -1 - @oldpos) < 0 then len(@line) else @pos - @oldpos end) 
   where pos = @oldpos

   set @counter = @counter + 1
    end

---------------------------------------------------------------------
-- Decode and cleanup the td htmlrows 
---------------------------------------------------------------------

    -- ID Correction for sql7, because it has no File version column, all ID's need to shift one place
    if @SqlVersion = '7' update @htmlRows set ID = ID + 1 where ID > 3

    -- Check for intersting flags (Latest SP & CU, RTM and New flags)
    if exists (select td from @htmlRows where td like '%Latest&nbsp;CU%')   insert into @htmlRows Values ((select max(ID) from @htmlRows) + 1, 10000, 0, 'Latest CU')
if exists (select td from @htmlRows where td like '%Latest&nbsp;SP%')   insert into @htmlRows Values ((select max(ID) from @htmlRows) + 1, 20000, 0, 'Latest SP')
if exists (select td from @htmlRows where td like '%<td class=rtm>%')   insert into @htmlRows Values ((select max(ID) from @htmlRows) + 1, 40000, 0, 'RTM')
if exists (select td from @htmlRows where td like '%*new%')insert into @htmlRows Values ((select max(ID) from @htmlRows) + 1, 30000, 0, '*new') 

    -- remove the unneeded html tag and class crap
    update @htmlRows set td = replace(td, '<td class=sp>', '')
    update @htmlRows set td = replace(td, '<td class=cu>', '')
    update @htmlRows set td = replace(td, '<td class=h>', '')
    update @htmlRows set td = replace(td, '<td>', '')
    update @htmlRows set td = replace(td, '</td>', '')
    update @htmlRows set td = replace(td, '<td class=rtm>', '')
    update @htmlRows set td = replace(td, '', '')
    update @htmlRows set td = replace(td, '', '')
    update @htmlRows set td = replace(td, '&nbsp; Latest&nbsp;SP', '')
    update @htmlRows set td = replace(td, '&nbsp; Latest&nbsp;CU', '')
    update @htmlRows set td = replace(td, '<font color="#FF0000" size="1"> *new</font>', '')

----Debug point------
    --select @line
    --select * from @htmlRows

--------------------------------------------------------------------------------
-- Extract the build, fileversion, KB, url etc and place them in their columns 
--------------------------------------------------------------------------------

-- Build
    UPDATE @out SET build = (select td from @htmlRows where ID = 1) WHERE CURRENT OF curOut

    -- ProductVersion
    UPDATE @out SET ProductVersion = (select td from @htmlRows where ID = 2) WHERE CURRENT OF curOut -- I reused the hidden column on the website for ProductVersion matching (after corrections... lot's of em....)

--Fileversion
    if @SqlVersion <> '7' 
    UPDATE @out SET fileversion = (select td from @htmlRows where ID = 3) WHERE CURRENT OF curOut  

    UPDATE @out SET kb = (select td from @htmlRows where ID = 5) WHERE CURRENT OF curOut  

    set @l = (select td from @htmlRows where ID = 6)
    
-- Description and url (if any)
    if left(@l, 8) = '<a href='
    begin
   UPDATE @out SET description = (select substring(@l, charindex('>', @l) +1 , charindex('<', @l, charindex('>', @l)) - charindex('>', @l) -1)) WHERE CURRENT OF curOut  
   UPDATE @out SET url = substring(@l, charindex('"', @l) + 1, charindex('"', @l, charindex('"', @l)+1) - charindex('"', @l) - 1) WHERE CURRENT OF curOut  
    end
    else
    begin
   UPDATE @out SET description = @l WHERE CURRENT OF curOut  
   UPDATE @out SET url = '' WHERE CURRENT OF curOut  
    end
    
-- And the rest....
    --UPDATE @out SET releasedate = (select td from @htmlRows where ID = 7) WHERE CURRENT OF curOut 
UPDATE @out SET releasedate = (select reverse(substring(reverse(td), 8, 10)) from @htmlRows where ID = 7) WHERE CURRENT OF curOut 
    UPDATE @out SET latest_sp = case when exists (select td from @htmlRows where pos = 20000) then 1 else 0 end WHERE CURRENT OF curOut
    UPDATE @out SET latest_cu = case when exists (select td from @htmlRows where pos = 10000) then 1 else 0 end WHERE CURRENT OF curOut
    UPDATE @out SET rtm = case when exists (select td from @htmlRows where pos = 40000) then 1 else 0 end WHERE CURRENT OF curOut
    UPDATE @out SET new  = case when exists (select td from @htmlRows where pos = 30000) then 1 else 0 end WHERE CURRENT OF curOut
    
-- Prepare for the next loop
delete from @htmlRows 

    FETCH NEXT FROM curOut INTO @ID, @LongSqlVersion, @SqlVersion, @htmlID, @line, @ProductVersion, @build, @fileversion, @KB, @description, @url, @releasedate, @latest_sp, @latest_cu, @rtm, @new 
END   

CLOSE curOut   
DEALLOCATE curOut

---------------------------------------------------------------------------------------------------------------------------------------------------
-- ProductVersion fixes, so you can use SERVERPROPERTY('ProductVersion') to match your SQL instance build to sqlserverbuilds.blogspot.com
---------------------------------------------------------------------------------------------------------------------------------------------------
----2019--
--set @ID = (select ID from @out where ProductVersion = '15.00.1200.?')
--Update @out SET ProductVersion = '15.00.1200.0' where ID = @ID -- the minor version does not support a ?
--Update @out SET fileversion = '2018.150.1200.0' where ID = @ID -- the file version does not support a ?
--Update @out SET build = '15.0.1200.0' where ID = @ID -- the build does not support a ?

--delete FROM [ServerInfo].[dbo].[tblBuildList] where ReleaseDate = '2018-12-11' -- fix after the fact 

--2019--
Update @out SET ProductVersion = build where SqlVersion = '2019' -- the minor version does not report a leading 0

--2017--
Update @out SET ProductVersion = build where SqlVersion = '2017' -- the minor version does not report a leading 0

--2016--
Update @out SET ProductVersion = build where SqlVersion = '2016' -- the minor version does not report a leading 0

--2014--
set @ID = (select ID from @out where ProductVersion = '12.0.5537 or 12.0.5538')
insert into @out select LongSqlVersion, SqlVersion, htmlID, line, '12.00.5538' ,build, fileversion, kb,description, url, releasedate, latest_sp, latest_cu, rtm, new FROM @out where ID = @ID
Update @out SET ProductVersion = '12.00.5537' where ID = @ID
Update @out SET ProductVersion = ProductVersion + '.0' where SqlVersion = '2014' and build <> '11.00.9120' -- attached .0 for consistent matching with using SERVERPROPERTY('ProductVersion') 
set @ID = (select ID from @out where ProductVersion = '12.0.4100.0') --2014 RTM SP1 can have a release value of 1
Update @out SET ProductVersion = '12.0.4100.1' where ID = @ID

--2012--
delete from @out where fileversion = '2011.110.9000.5' -- Extremely rare version with a non-standard build the makes sorting a pain.... i vote to drop it :)
Update @out SET ProductVersion = ProductVersion + '.0' where SqlVersion = '2012' or build = '11.00.9120' -- attached .0 for consistent matching with using SERVERPROPERTY('ProductVersion') 

--2008r2--
Update @out SET ProductVersion = build + '.0' where SqlVersion = '2008r2' -- r2 was missing in the hidden column on the website, attached .0 for consistent matching with using SERVERPROPERTY('ProductVersion') 
set @ID = (select ID from @out where ProductVersion = '10.50.6000.0') --r2 SP3 can have a release of 34
Update @out SET ProductVersion = '10.50.6000.34' where ID = @ID
set @ID = (select ID from @out where ProductVersion = '10.50.1600.0') --r2 RTM SP1 can have a release of 1
Update @out SET ProductVersion = '10.50.1600.1' where ID = @ID

--2008--
Update @out SET ProductVersion = ProductVersion + '.0' where SqlVersion = '2008' -- the minor version does not report a leading 0, attached .0 for consistent matching with using SERVERPROPERTY('ProductVersion') 

--2005--
Update @out SET ProductVersion = build + '.00' where SqlVersion = '2005' -- attached .0 for consistent matching with using SERVERPROPERTY('ProductVersion') 
Update @out SET latest_cu = 1 where fileversion = '2005.90.5266.0' -- attached .0 for consistent matching with using SERVERPROPERTY('ProductVersion') 

--2000--
Update @out SET ProductVersion = build + '.0' where SqlVersion = '2000' -- attached .0 for consistent matching with using SERVERPROPERTY('ProductVersion') 

--7--
Update @out SET ProductVersion = build + '.0' where SqlVersion = '7' -- attached .0 for consistent matching with using SERVERPROPERTY('ProductVersion') 


--------------------------------------------------------------------------------
-- Step 2 : save @out into tblBuildListImport and update tblBuildList 
--------------------------------------------------------------------------------
IF OBJECT_ID('dbo.tblBuildListImport', 'U') IS NOT NULL DROP TABLE dbo.tblBuildListImport; 

-- Used for the alert email
declare @mailbody varchar(1000) = 'See https://as-ordsql001.azurewebsites.net/SqlBuilds2.aspx for details' 
    + char(13) + char(10) + 
    'and https://as-ordsql001.azurewebsites.net/Reports/report/Server%20info/Customer%20SQL%20Version for customer impact'
-- OLA, 07-08-2020, webadres naar Reports werkt nog niet!

--------------------------------------------------------------------------------
-- Extraction and corrections complete, save into the tblBuildListImport
--------------------------------------------------------------------------------
select  *
INTO [dbo].[tblBuildListImport]
FROM@out
ORDER BY htmlID

--------------------------------------------------------------------------------
-- add computed coumns for easy sorting
--------------------------------------------------------------------------------
--ALTER TABLE dbo.tblBuildListImport ADD
--Major  AS (CONVERT([int],case when parsename([build],(4))>=(13)then parsename([build],(4)) else parsename([build],(3)) end)),
--Minor  AS (CONVERT([int],case when parsename([build],(4))>=(13) then parsename([build],(3)) else parsename([build],(2)) end)),
--BuildNr  AS (CONVERT([int],case when parsename([build],(4))>=(13) then parsename([build],(2)) else parsename([build],(1)) end)),
--Revision  AS (CONVERT([int],case when parsename([build],(4))>=(13) then parsename([build],(1))  end))

ALTER TABLE dbo.tblBuildListImport ADD
    [Major] AS (convert(int, reverse(parsename(reverse([build]), 1)))),
[Minor] AS (convert(int, reverse(parsename(reverse([build]), 2)))),
[BuildNr] AS (convert(int, reverse(parsename(reverse([build]), 3)))),
[Revision] AS (isnull(convert(int, reverse(parsename(reverse([build]), 4))), 0))

--------------------------------------------------------------------------------
-- Save new build records in tblBuildList
--------------------------------------------------------------------------------
INSERT INTO [dbo].[tblBuildList]
           ([Version]
 ,l.[ProductVersion]
           ,[Build]
           ,[FileVersion]
           ,[KBDescription]
 ,l.
           ,[ReleaseDate]
           ,[SP]
           ,[CU]
           ,[HF]
           ,[RTM]
           ,[CTP]
 ,[LatestSP]
 ,[LatestCU]
 ,[New]
           ,[Comment])
SELECT upper([SqlVersion])
 ,i.[ProductVersion]
      ,[build]
      ,[fileversion]
      ,[description]
 ,i.
      ,[releasedate]
 ,0 as SP
 ,0 as CU
 ,0 as HF
 ,[rtm]
 ,0 as CTP
 ,latest_sp
 ,latest_cu
 ,new
 ,' auto add' as Cmt
FROM[dbo].[tblBuildListImport] i
left join dbo.tblBuildList l on i.build = l.Build 
where l.ID is null
ORDER BY htmlID 


--------------------------------------------------------------------------------
-- Send email if new records are found
--------------------------------------------------------------------------------
if @@ROWCOUNT > 0
execute msdb.dbo.sp_send_dbmail
 @profile_name = 'Ordina'
,@recipients = 'mssql@ordina.nl'
,@subject = 'New patches on http://sqlserverbuilds.blogspot.com detected'
,@body = @mailbody
,@body_format = 'HTML' -- or TEXT
,@importance = 'Normal' --Low Normal High
,@sensitivity = 'Normal' --Normal Personal Private Confidential


--------------------------------------------------------------------------------
-- Update info (Because it could change)
--------------------------------------------------------------------------------
update[dbo].[tblBuildList]
set [ProductVersion] = i.[ProductVersion]
,[Version] = UPPER([Version])
, = i.
,[LatestSP] = i.[latest_sp]
,[LatestCU] = i.[latest_cu]
,[New] = i.[new]
FROM[dbo].[tblBuildListImport] i
left join [dbo].[tblBuildList] l 
on i.build = l.Build 

--------------------------------------------------------------------------------
-- Update flags SP, CU, HF and CTP flags (extra search fields)
--------------------------------------------------------------------------------
update  dbo.tblBuildList
set   SP = 1 
where  UPPER(KBDescription) like UPPER('%Service Pack 17 (SP17)')
or   UPPER(KBDescription) like UPPER('%Service Pack 16 (SP16)')
or   UPPER(KBDescription) like UPPER('%Service Pack 15 (SP15)')
or   UPPER(KBDescription) like UPPER('%Service Pack 14 (SP14)')
or   UPPER(KBDescription) like UPPER('%Service Pack 13 (SP13)')
or   UPPER(KBDescription) like UPPER('%Service Pack 12 (SP12)')
or   UPPER(KBDescription) like UPPER('%Service Pack 11 (SP11)')
or   UPPER(KBDescription) like UPPER('%Service Pack 10 (SP10)')
or   UPPER(KBDescription) like UPPER('%Service Pack 9 (SP9)')
or   UPPER(KBDescription) like UPPER('%Service Pack 8 (SP7)')
or   UPPER(KBDescription) like UPPER('%Service Pack 7 (SP7)')
or   UPPER(KBDescription) like UPPER('%Service Pack 6 (SP6)')
or   UPPER(KBDescription) like UPPER('%Service Pack 5 (SP5)')
or   UPPER(KBDescription) like UPPER('%Service Pack 4 (SP4)')
or   UPPER(KBDescription) like UPPER('%Service Pack 3 (SP3)')
or   UPPER(KBDescription) like UPPER('%Service Pack 2 (SP2)')
or   UPPER(KBDescription) like UPPER('%Service Pack 1 (SP1)')

update  dbo.tblBuildList
set   CU = 1 
where  UPPER(KBDescription) like UPPER('%Cumulative update 33 (CU33)%')
or     UPPER(KBDescription) like UPPER('%Cumulative update 32 (CU32)%')
or     UPPER(KBDescription) like UPPER('%Cumulative update 31 (CU31)%')
or     UPPER(KBDescription) like UPPER('%Cumulative update 30 (CU30)%')
or     UPPER(KBDescription) like UPPER('%Cumulative update 29 (CU29)%')
or     UPPER(KBDescription) like UPPER('%Cumulative update 28 (CU28)%')
or     UPPER(KBDescription) like UPPER('%Cumulative update 27 (CU27)%')
or     UPPER(KBDescription) like UPPER('%Cumulative update 26 (CU26)%')
or     UPPER(KBDescription) like UPPER('%Cumulative update 25 (CU25)%')
or     UPPER(KBDescription) like UPPER('%Cumulative update 24 (CU24)%')
or     UPPER(KBDescription) like UPPER('%Cumulative update 23 (CU23)%')
or     UPPER(KBDescription) like UPPER('%Cumulative update 22 (CU22)%')
or     UPPER(KBDescription) like UPPER('%Cumulative update 21 (CU21)%')
or     UPPER(KBDescription) like UPPER('%Cumulative update 20 (CU20)%')
or   UPPER(KBDescription) like UPPER('%Cumulative update 19 (CU19)%')
or   UPPER(KBDescription) like UPPER('%Cumulative update 18 (CU18)%')
or   UPPER(KBDescription) like UPPER('%Cumulative update 17 (CU17)%')
or   UPPER(KBDescription) like UPPER('%Cumulative update 16 (CU16)%')
or   UPPER(KBDescription) like UPPER('%Cumulative update 15 (CU15)%')
or   UPPER(KBDescription) like UPPER('%Cumulative update 14 (CU14)%')
or   UPPER(KBDescription) like UPPER('%Cumulative update 13 (CU13)%')
or   UPPER(KBDescription) like UPPER('%Cumulative update 12 (CU12)%')
or   UPPER(KBDescription) like UPPER('%Cumulative update 11 (CU11)%')
or   UPPER(KBDescription) like UPPER('%Cumulative update 10 (CU10)%')
or   UPPER(KBDescription) like UPPER('%Cumulative update 9 (CU9)%')
or   UPPER(KBDescription) like UPPER('%Cumulative update 8 (CU8)%')
or   UPPER(KBDescription) like UPPER('%Cumulative update 7 (CU7)%')
or   UPPER(KBDescription) like UPPER('%Cumulative update 6 (CU6)%')
or   UPPER(KBDescription) like UPPER('%Cumulative update 5 (CU5)%')
or   UPPER(KBDescription) like UPPER('%Cumulative update 4 (CU4)%')
or   UPPER(KBDescription) like UPPER('%Cumulative update 3 (CU3)%')
or   UPPER(KBDescription) like UPPER('%Cumulative update 2 (CU2)%')
or   UPPER(KBDescription) like UPPER('%Cumulative update 1 (CU1)%')

update  dbo.tblBuildList
set   CU = 1 
where  UPPER(KBDescription) like UPPER('%Cumulative update package 33 (CU33)%')
or   UPPER(KBDescription) like UPPER('%Cumulative update package 32 (CU32)%')
or   UPPER(KBDescription) like UPPER('%Cumulative update package 31 (CU31)%')
or   UPPER(KBDescription) like UPPER('%Cumulative update package 30 (CU30)%')
or   UPPER(KBDescription) like UPPER('%Cumulative update package 29 (CU29)%')
or   UPPER(KBDescription) like UPPER('%Cumulative update package 28 (CU28)%')
or   UPPER(KBDescription) like UPPER('%Cumulative update package 27 (CU27)%')
or   UPPER(KBDescription) like UPPER('%Cumulative update package 26 (CU26)%')
or   UPPER(KBDescription) like UPPER('%Cumulative update package 25 (CU25)%')
or   UPPER(KBDescription) like UPPER('%Cumulative update package 24 (CU24)%')
or   UPPER(KBDescription) like UPPER('%Cumulative update package 23 (CU23)%')
or   UPPER(KBDescription) like UPPER('%Cumulative update package 22 (CU22)%')
or   UPPER(KBDescription) like UPPER('%Cumulative update package 21 (CU21)%')
or   UPPER(KBDescription) like UPPER('%Cumulative update package 20 (CU20)%')
or   UPPER(KBDescription) like UPPER('%Cumulative update package 19 (CU19)%')
or   UPPER(KBDescription) like UPPER('%Cumulative update package 18 (CU18)%')
or   UPPER(KBDescription) like UPPER('%Cumulative update package 17 (CU17)%')
or   UPPER(KBDescription) like UPPER('%Cumulative update package 16 (CU16)%')
or   UPPER(KBDescription) like UPPER('%Cumulative update package 15 (CU15)%')
or   UPPER(KBDescription) like UPPER('%Cumulative update package 14 (CU14)%')
or   UPPER(KBDescription) like UPPER('%Cumulative update package 13 (CU13)%')
or   UPPER(KBDescription) like UPPER('%Cumulative update package 12 (CU12)%')
or   UPPER(KBDescription) like UPPER('%Cumulative update package 11 (CU11)%')
or   UPPER(KBDescription) like UPPER('%Cumulative update package 10 (CU10)%')
or   UPPER(KBDescription) like UPPER('%Cumulative update package 9 (CU9)%')
or   UPPER(KBDescription) like UPPER('%Cumulative update package 8 (CU8)%')
or   UPPER(KBDescription) like UPPER('%Cumulative update package 7 (CU7)%')
or   UPPER(KBDescription) like UPPER('%Cumulative update package 6 (CU6)%')
or   UPPER(KBDescription) like UPPER('%Cumulative update package 5 (CU5)%')
or   UPPER(KBDescription) like UPPER('%Cumulative update package 4 (CU4)%')
or   UPPER(KBDescription) like UPPER('%Cumulative update package 3 (CU3)%')
or   UPPER(KBDescription) like UPPER('%Cumulative update package 2 (CU2)%')
or   UPPER(KBDescription) like UPPER('%Cumulative update package 1 (CU1)%')

update  dbo.tblBuildList
set   HF = 1 
where   UPPER(KBDescription) like UPPER('%Hotfix%')

update  dbo.tblBuildList
set   CTP = 1 
where   UPPER(KBDescription) like UPPER('%Community Technology Preview%')

update  dbo.tblBuildList
set   CTP = 1 
where   UPPER(KBDescription) like UPPER('%Release Candidate%')

--------------------------------------------------------------------------------
-- Done
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
-- Time to select what you need, or use the table for automated patch checks
--------------------------------------------------------------------------------

---- I.E. The latest SP and CU per SQL version
--select  *
--from   dbo.tblBuildList
--where   LatestSP = 1 
--or    LatestCU = 1
--ORDER BY Major desc
--   ,Minor desc
--   ,BuildNr desc
--   ,Revision desc

@christianl Can you try this version of the code?

use [ServerInfo]
go

/******************************************************************************************
This script returns the info on http://sqlserverbuilds.blogspot.com as a table
*******************************************************************************************
Version:  1.0
 Author:  Theo Ekelmans
  Email:  theo@ekelmans.com
   Date:  2017-05-09
   
Version:  1.1
 Author:  Theo Ekelmans
   Date:  2019-01-09
 Change:  Small fixes due to some changes on sqlserverbuilds.blogspot.com

Version:  1.2
 Author:  Theo Ekelmans
   Date:  2019-003-13
 Change:  Small fixes due to some json artifacts   

Version:  1.3
 Author:  Onno Lagerwerf
  Email:  onno.lagerwerf@ordina.nl
   Date:  2020-08-07
 Change:   
- html h1 search entry changed in h2
- releasedate from html to date format
- mail pointing to different servers
- removed 'serverinfo' from objects
- extende Production version fixes
- alter major/minor.... calculated columns

Version:  1.4
 Author:  Theo Ekelmans
  Email:  theo@ekelmans.com
   Date:  2017-05-09
 Change:  Added some extra CU lines as we have passed CU21, which I did not expect :)

*******************************************************************************************
This script uses wget.exe from this excellent site: https://eternallybored.org/misc/wget/
Save the wget.exe file in the same folder as the ERRORLOG file (or change the path below)
******************************************************************************************/
set nocount on

-- Lots of royally sized vars :)
begin
declare @out table (
    ID int identity(1,1) PRIMARY KEY, -- All tables need an ID, right?
    LongSqlVersion varchar(1000), -- The header text of each SQL version
    SqlVersion varchar(1000), -- 7, 2000, 2005, 2008, 2008R2, 2012, 2016, 2017 etc.
    htmlID int, -- The order in which data is read from sqlserverbuilds.blogspot.com
    line varchar(8000),  -- The raw data from sqlserverbuilds.blogspot.com
    ProductVersion varchar(1000), -- This is the column to match the output of SERVERPROPERTY('ProductVersion') against, i have fixed all the exceptions i could find , thanks to MS for being so consistent .**NOT** :)
    build varchar(1000),  -- The build label that sqlserverbuilds.blogspot.com uses (this is not always the same as SERVERPROPERTY(ProductVersion'') reports), use this columnt together with the new columt to update your local table
    fileversion varchar(1000), -- Don't ask, i have no idea....
    kb varchar(1000),  -- The knowledgebase article for this patch
    description varchar(1000),  -- The knowledgebase description for this patch
    url varchar(1000), -- The knowledgebase download link for this patch
    releasedate varchar(1000), -- Guess....
    latest_sp bit, -- This bit is high for the latest SP for this SqlVersion (and the reason why i built this script)
    latest_cu bit, -- This bit is high for the latest CU for this SqlVersion (and the reason why i built this script)
    rtm bit, -- This bit is high is this build is the RTM (release to market) version, and most definitly not the build you want to be on!
    new bit) -- If you know what were the latest released builds? Say hello to yopur little bitty friend :)
declare @Cmd varchar(1000) 
declare @Path varchar(1000)
declare @FileName varchar (1024)
declare @OLEResult int
declare @FS int
declare @FileID int
declare @Message varchar (8000)
declare @LongSqlVersion as varchar(250)
declare @CurrentSqlVersion as varchar(20)
declare @ID int
declare @SqlVersion varchar(20)
declare @StartLine int
declare @EndLine int
declare @htmlID int
declare @line varchar(8000)
declare @ProductVersion varchar(1000)
declare @build varchar(1000)
declare @fileversion varchar(1000)
declare @KB varchar(1000)
declare @description varchar(1000)
declare @url varchar(1250)
declare @releasedate varchar(1000)
declare @latest_sp bit
declare @latest_cu bit
declare @rtm bit
declare @new bit
declare @pos int
declare @oldpos int
declare @counter int
declare @l varchar(1000) 
declare @htmlLine table(
[ID] [int] identity(1,1) NOT NULL, 
line varchar(max))
declare @SqlVersionTables table(
ID int identity(1,1) PRIMARY KEY,
LongSqlVersion varchar(250), 
SqlVersion varchar(20), 
StartLine int, 
EndLine int)
declare @html table(
[ID] [int] identity(1,1) NOT NULL, 
line varchar(max))
declare @htmlRows table (
ID int PRIMARY KEY, 
pos int, 
epos int, 
td varchar(8000))
end

-- Get SQL errorlog path (a convenient place to store WGET.EXE and the out.html files)
set @Path = (select substring(cast(SERVERPROPERTY('ErrorLogFileName') as varchar(256)), 1 , len(cast(SERVERPROPERTY('ErrorLogFileName') as varchar(256))) - 8))
set @FileName = @Path+'out.html'

-- Go get it (html output goes to the out.html)
set @Cmd = 'CMD /S /C " "'+@Path+'wget.exe" --quiet -O "'+@Path+'out.html" http://sqlserverbuilds.blogspot.nl " '
exec xp_cmdshell @Cmd, no_output 

-- Create an instance of the file system object
EXECUTE @OLEResult = sp_OACreate 'Scripting.FileSystemObject', @FS OUT
IF @OLEResult <> 0
BEGIN
PRINT 'Scripting.FileSystemObject'
PRINT 'Error code: ' + CONVERT (varchar, @OLEResult)
END

-- Open the out.htmlfile for reading
EXEC @OLEResult = sp_OAMethod @FS, 'OpenTextFile', @FileID OUT, @FileName, 1, 1
IF @OLEResult <> 0
BEGIN
PRINT 'OpenTextFile'
PRINT 'Error code: ' + CONVERT (varchar, @OLEResult)
END

-- Read the first line into the @Message variable
EXECUTE @OLEResult = sp_OAMethod @FileID, 'ReadLine', @Message OUT

-- Keep looping through the file until the @OLEResult variable is < 0; this indicates that the end of the file has been reached.
WHILE @OLEResult >= 0
BEGIN
-- Save each line into a table variable
insert into @html(line)
select @Message

EXECUTE @OLEResult = sp_OAMethod @FileID, 'ReadLine', @Message OUT
END

-- Clean up
EXECUTE @OLEResult = sp_OADestroy @FileID
EXECUTE @OLEResult = sp_OADestroy @FS

----Debug point------
-- select * from @html 

---------------------------------------------------------------------
-- Check at what line each of the SQL version tables start and end
---------------------------------------------------------------------
insert into @SqlVersionTables 
selectSUBSTRING(line, CHARINDEX('>', line, 8) + 1, len(line) - 6 - CHARINDEX('>', line, 8) + 1) as [LongSqlVersion],
SUBSTRING(line, 11, CHARINDEX('>', line, 8) -11) as [SqlVersion], 
ID, 
0 
from@html 
whereline like '%<h2 id=sql%'

update @SqlVersionTables
set EndLine = ( select top 1 ID
from @html 
where ID > s.StartLine
and line like '</table>%'
order by ID) 
from @SqlVersionTables s

----Debug point------
-- select * from @SqlVersionTables 
    
------------------------------------------------------------------------
-- Extract html lines for each of the sql version tables 
------------------------------------------------------------------------
declarecurSqlVer CURSOR FOR  
select ID
,LongSqlVersion
,SqlVersion
,StartLine
,EndLine
from@SqlVersionTables 

OPEN curSqlVer   
FETCH NEXT FROM curSqlVer INTO @ID, @LongSqlVersion, @CurrentSqlVersion, @StartLine, @EndLine 

WHILE @@FETCH_STATUS = 0   
BEGIN   

    insert into @out 
    select  @LongSqlVersion 
,@CurrentSqlVersion 
,ID
,replace(replace(line, '</tr>', ''), '<tr>', '') as line -- strip the tr tags
,'' as ProductVersion
,'' as build
,'' as fv
,'' as kb
,'' as descr
,'' as url 
,getdate() as rd
,0 as lsp 
,0 as lcu
,0 as rtm 
,0 as new
    from   @html
    where   ID between (select StartLine from @SqlVersionTables where SqlVersion = @CurrentSqlVersion ) + 1
       and (select EndLine   from @SqlVersionTables where SqlVersion = @CurrentSqlVersion ) 
    and   line like '<tr><td%' -- Only the rows of the tables are interesting
    and   len(line) > 0        -- IF they are filled 
    order by ID

    FETCH NEXT FROM curSqlVer INTO @ID, @LongSqlVersion, @CurrentSqlVersion, @StartLine, @EndLine    
END   

CLOSE curSqlVer   
DEALLOCATE curSqlVer

---------------------------------------------
-- Loop thought the table rows, stip all html tags 
---------------------------------------------

declare curOut CURSOR FOR 
select  ID, 
   LongSqlVersion, 
   SqlVersion, 
   htmlID, 
   line, 
   ProductVersion,
   build, 
   fileversion, 
   kb,
   description, 
   url, 
   releasedate, 
   latest_sp, 
   latest_cu, 
   rtm,
   new
FROM   @out
ORDER BY htmlID
FOR UPDATE

OPEN curOut   
FETCH NEXT FROM curOut INTO @ID, @LongSqlVersion, @SqlVersion, @htmlID, @line, @ProductVersion, @build, @fileversion, @KB, @description, @url, @releasedate, @latest_sp, @latest_cu, @rtm, @new 

WHILE @@FETCH_STATUS = 0   
BEGIN   
    set @counter = 1 
    set @oldpos=0
    set @pos=patindex('%<td%',@line) 

-- Loop through the chars in the html row and find the start of every td tag
-- Insert every td into a row in @htmlrows
    while @pos > 0 and @oldpos<>@pos
    begin
   insert into @htmlRows Values (@counter, @pos, 0, '')

   set @oldpos=@pos
   set @pos=patindex('%<td%',Substring(@line,@pos + 1,len(@line))) + @pos
   
   update @htmlRows 
   set  epos = case when @oldpos=@pos then len(@line) else @pos -1 end 
  ,td = substring(@line, @oldpos, case when (@pos -1 - @oldpos) < 0 then len(@line) else @pos - @oldpos end) 
   where pos = @oldpos

   set @counter = @counter + 1
    end

---------------------------------------------------------------------
-- Decode and cleanup the td htmlrows 
---------------------------------------------------------------------

    -- ID Correction for sql7, because it has no File version column, all ID's need to shift one place
    if @SqlVersion = '7' update @htmlRows set ID = ID + 1 where ID > 3

    -- Check for intersting flags (Latest SP & CU, RTM and New flags)
    if exists (select td from @htmlRows where td like '%Latest&nbsp;CU%')   insert into @htmlRows Values ((select max(ID) from @htmlRows) + 1, 10000, 0, 'Latest CU')
if exists (select td from @htmlRows where td like '%Latest&nbsp;SP%')   insert into @htmlRows Values ((select max(ID) from @htmlRows) + 1, 20000, 0, 'Latest SP')
if exists (select td from @htmlRows where td like '%<td class=rtm>%')   insert into @htmlRows Values ((select max(ID) from @htmlRows) + 1, 40000, 0, 'RTM')
if exists (select td from @htmlRows where td like '%*new%')insert into @htmlRows Values ((select max(ID) from @htmlRows) + 1, 30000, 0, '*new') 

    -- remove the unneeded html tag and class crap
    update @htmlRows set td = replace(td, '<td class=sp>', '')
    update @htmlRows set td = replace(td, '<td class=cu>', '')
    update @htmlRows set td = replace(td, '<td class=h>', '')
    update @htmlRows set td = replace(td, '<td>', '')
    update @htmlRows set td = replace(td, '</td>', '')
    update @htmlRows set td = replace(td, '<td class=rtm>', '')
    update @htmlRows set td = replace(td, '', '')
    update @htmlRows set td = replace(td, '', '')
    update @htmlRows set td = replace(td, '&nbsp; Latest&nbsp;SP', '')
    update @htmlRows set td = replace(td, '&nbsp; Latest&nbsp;CU', '')
    update @htmlRows set td = replace(td, '<font color="#FF0000" size="1"> *new</font>', '')
    update @htmlRows set td = replace(td, '&quot;', '"') -- JSON does *not* like &quot;


update @htmlRows set td = replace(td, '', '-') -- JSON does *not* like span;
update @htmlRows set td = replace(td, '', '-') -- JSON does *not* like span;


    update @htmlRows set td = replace(td, '<time datetime="', '') -- DT prefix;

----Debug point------
    --select @line
    --select * from @htmlRows

--------------------------------------------------------------------------------
-- Extract the build, fileversion, KB, url etc and place them in their columns 
--------------------------------------------------------------------------------

-- Build
    UPDATE @out SET build = (select td from @htmlRows where ID = 1) WHERE CURRENT OF curOut

    -- ProductVersion
    UPDATE @out SET ProductVersion = (select td from @htmlRows where ID = 2) WHERE CURRENT OF curOut -- I reused the hidden column on the website for ProductVersion matching (after corrections... lot's of em....)

--Fileversion
    if @SqlVersion <> '7' 
    UPDATE @out SET fileversion = (select td from @htmlRows where ID = 3) WHERE CURRENT OF curOut  

    UPDATE @out SET kb = (select td from @htmlRows where ID = 5) WHERE CURRENT OF curOut  

    set @l = (select td from @htmlRows where ID = 6)
    
-- Description and url (if any)
    if left(@l, 8) = '<a href='
    begin
   UPDATE @out SET description = (select substring(@l, charindex('>', @l) +1 , charindex('<', @l, charindex('>', @l)) - charindex('>', @l) -1)) WHERE CURRENT OF curOut  
   UPDATE @out SET url = substring(@l, charindex('"', @l) + 1, charindex('"', @l, charindex('"', @l)+1) - charindex('"', @l) - 1) WHERE CURRENT OF curOut  
    end
    else
    begin
   UPDATE @out SET description = @l WHERE CURRENT OF curOut  
   UPDATE @out SET url = '' WHERE CURRENT OF curOut  
    end
    
-- And the rest....
    --UPDATE @out SET releasedate = (select td from @htmlRows where ID = 7) WHERE CURRENT OF curOut 
UPDATE @out SET releasedate = (select reverse(substring(reverse(td), 8, 10)) from @htmlRows where ID = 7) WHERE CURRENT OF curOut 
    UPDATE @out SET latest_sp = case when exists (select td from @htmlRows where pos = 20000) then 1 else 0 end WHERE CURRENT OF curOut
    UPDATE @out SET latest_cu = case when exists (select td from @htmlRows where pos = 10000) then 1 else 0 end WHERE CURRENT OF curOut
    UPDATE @out SET rtm = case when exists (select td from @htmlRows where pos = 40000) then 1 else 0 end WHERE CURRENT OF curOut
    UPDATE @out SET new  = case when exists (select td from @htmlRows where pos = 30000) then 1 else 0 end WHERE CURRENT OF curOut
    
-- Prepare for the next loop
delete from @htmlRows 

    FETCH NEXT FROM curOut INTO @ID, @LongSqlVersion, @SqlVersion, @htmlID, @line, @ProductVersion, @build, @fileversion, @KB, @description, @url, @releasedate, @latest_sp, @latest_cu, @rtm, @new 
END   

CLOSE curOut   
DEALLOCATE curOut

---------------------------------------------------------------------------------------------------------------------------------------------------
-- ProductVersion fixes, so you can use SERVERPROPERTY('ProductVersion') to match your SQL instance build to sqlserverbuilds.blogspot.com
---------------------------------------------------------------------------------------------------------------------------------------------------
----2019--
--set @ID = (select ID from @out where ProductVersion = '15.00.1200.?')
--Update @out SET ProductVersion = '15.00.1200.0' where ID = @ID -- the minor version does not support a ?
--Update @out SET fileversion = '2018.150.1200.0' where ID = @ID -- the file version does not support a ?
--Update @out SET build = '15.0.1200.0' where ID = @ID -- the build does not support a ?

--delete FROM [ServerInfo].[dbo].[tblBuildList] where ReleaseDate = '2018-12-11' -- fix after the fact 

--2019--
Update @out SET ProductVersion = build where SqlVersion = '2019' -- the minor version does not report a leading 0

--2017--
Update @out SET ProductVersion = build where SqlVersion = '2017' -- the minor version does not report a leading 0

--2016--
Update @out SET ProductVersion = build where SqlVersion = '2016' -- the minor version does not report a leading 0

--2014--
set @ID = (select ID from @out where ProductVersion = '12.0.5537 or 12.0.5538')
insert into @out select LongSqlVersion, SqlVersion, htmlID, line, '12.00.5538' ,build, fileversion, kb,description, url, releasedate, latest_sp, latest_cu, rtm, new FROM @out where ID = @ID
Update @out SET ProductVersion = '12.00.5537' where ID = @ID
Update @out SET ProductVersion = ProductVersion + '.0' where SqlVersion = '2014' and build <> '11.00.9120' -- attached .0 for consistent matching with using SERVERPROPERTY('ProductVersion') 
set @ID = (select ID from @out where ProductVersion = '12.0.4100.0') --2014 RTM SP1 can have a release value of 1
Update @out SET ProductVersion = '12.0.4100.1' where ID = @ID

--2012--
delete from @out where fileversion = '2011.110.9000.5' -- Extremely rare version with a non-standard build the makes sorting a pain.... i vote to drop it :)
Update @out SET ProductVersion = ProductVersion + '.0' where SqlVersion = '2012' or build = '11.00.9120' -- attached .0 for consistent matching with using SERVERPROPERTY('ProductVersion') 

--2008r2--
Update @out SET ProductVersion = build + '.0' where SqlVersion = '2008r2' -- r2 was missing in the hidden column on the website, attached .0 for consistent matching with using SERVERPROPERTY('ProductVersion') 
set @ID = (select ID from @out where ProductVersion = '10.50.6000.0') --r2 SP3 can have a release of 34
Update @out SET ProductVersion = '10.50.6000.34' where ID = @ID
set @ID = (select ID from @out where ProductVersion = '10.50.1600.0') --r2 RTM SP1 can have a release of 1
Update @out SET ProductVersion = '10.50.1600.1' where ID = @ID

--2008--
Update @out SET ProductVersion = ProductVersion + '.0' where SqlVersion = '2008' -- the minor version does not report a leading 0, attached .0 for consistent matching with using SERVERPROPERTY('ProductVersion') 

--2005--
Update @out SET ProductVersion = build + '.00' where SqlVersion = '2005' -- attached .0 for consistent matching with using SERVERPROPERTY('ProductVersion') 
Update @out SET latest_cu = 1 where fileversion = '2005.90.5266.0' -- attached .0 for consistent matching with using SERVERPROPERTY('ProductVersion') 

--2000--
Update @out SET ProductVersion = build + '.0' where SqlVersion = '2000' -- attached .0 for consistent matching with using SERVERPROPERTY('ProductVersion') 

--7--
Update @out SET ProductVersion = build + '.0' where SqlVersion = '7' -- attached .0 for consistent matching with using SERVERPROPERTY('ProductVersion') 


--------------------------------------------------------------------------------
-- Step 2 : save @out into tblBuildListImport and update tblBuildList 
--------------------------------------------------------------------------------
IF OBJECT_ID('dbo.tblBuildListImport', 'U') IS NOT NULL DROP TABLE dbo.tblBuildListImport; 

-- Used for the alert email
declare @mailbody varchar(1000) = 'See https://as-ordsql001.azurewebsites.net/SqlBuilds2.aspx for details' 
    + char(13) + char(10) + 
    'and https://as-ordsql001.azurewebsites.net/Reports/report/Server%20info/Customer%20SQL%20Version for customer impact'
-- OLA, 07-08-2020, webadres naar Reports werkt nog niet!

--------------------------------------------------------------------------------
-- Extraction and corrections complete, save into the tblBuildListImport
--------------------------------------------------------------------------------
select  *
INTO [dbo].[tblBuildListImport]
FROM@out
ORDER BY htmlID

--------------------------------------------------------------------------------
-- add computed coumns for easy sorting
--------------------------------------------------------------------------------
ALTER TABLE dbo.tblBuildListImport ADD
    [Major] AS (convert(int, reverse(parsename(reverse([build]), 1)))),
[Minor] AS (convert(int, reverse(parsename(reverse([build]), 2)))),
[BuildNr] AS (convert(int, reverse(parsename(reverse([build]), 3)))),
[Revision] AS (isnull(convert(int, reverse(parsename(reverse([build]), 4))), 0))

--------------------------------------------------------------------------------
-- Save new build records in tblBuildList
--------------------------------------------------------------------------------
INSERT INTO [dbo].[tblBuildList]
           ([Version]
 ,l.[ProductVersion]
           ,[Build]
           ,[FileVersion]
           ,[KBDescription]
 ,l.
           ,[ReleaseDate]
           ,[SP]
           ,[CU]
           ,[HF]
           ,[RTM]
           ,[CTP]
 ,[LatestSP]
 ,[LatestCU]
 ,[New]
           ,[Comment])
SELECT upper([SqlVersion])
 ,i.[ProductVersion]
      ,[build]
      ,[fileversion]
      ,[description]
 ,i.
      ,[releasedate]
 ,0 as SP
 ,0 as CU
 ,0 as HF
 ,[rtm]
 ,0 as CTP
 ,latest_sp
 ,latest_cu
 ,new
 ,' auto add' as Cmt
FROM[dbo].[tblBuildListImport] i
left join dbo.tblBuildList l on i.build = l.Build 
where l.ID is null
ORDER BY htmlID 


--------------------------------------------------------------------------------
-- Send email if new records are found
--------------------------------------------------------------------------------
if @@ROWCOUNT > 0
execute msdb.dbo.sp_send_dbmail
 @profile_name = 'Ordina'
,@recipients = 'mssql@ordina.nl'
,@subject = 'New patches on http://sqlserverbuilds.blogspot.com detected'
,@body = @mailbody
,@body_format = 'HTML' -- or TEXT
,@importance = 'Normal' --Low Normal High
,@sensitivity = 'Normal' --Normal Personal Private Confidential


--------------------------------------------------------------------------------
-- Update info (Because it could change)
--------------------------------------------------------------------------------
update[dbo].[tblBuildList]
set [ProductVersion] = i.[ProductVersion]
,[Version] = UPPER([Version])
, = i.
,[LatestSP] = i.[latest_sp]
,[LatestCU] = i.[latest_cu]
,[New] = i.[new]
FROM[dbo].[tblBuildListImport] i
left join [dbo].[tblBuildList] l 
on i.build = l.Build 

--------------------------------------------------------------------------------
-- Update flags SP, CU, HF and CTP flags (extra search fields)
--------------------------------------------------------------------------------
update  dbo.tblBuildList
set   SP = 1 
where  UPPER(KBDescription) like UPPER('%Service Pack 17 (SP17)')
or   UPPER(KBDescription) like UPPER('%Service Pack 16 (SP16)')
or   UPPER(KBDescription) like UPPER('%Service Pack 15 (SP15)')
or   UPPER(KBDescription) like UPPER('%Service Pack 14 (SP14)')
or   UPPER(KBDescription) like UPPER('%Service Pack 13 (SP13)')
or   UPPER(KBDescription) like UPPER('%Service Pack 12 (SP12)')
or   UPPER(KBDescription) like UPPER('%Service Pack 11 (SP11)')
or   UPPER(KBDescription) like UPPER('%Service Pack 10 (SP10)')
or   UPPER(KBDescription) like UPPER('%Service Pack 9 (SP9)')
or   UPPER(KBDescription) like UPPER('%Service Pack 8 (SP7)')
or   UPPER(KBDescription) like UPPER('%Service Pack 7 (SP7)')
or   UPPER(KBDescription) like UPPER('%Service Pack 6 (SP6)')
or   UPPER(KBDescription) like UPPER('%Service Pack 5 (SP5)')
or   UPPER(KBDescription) like UPPER('%Service Pack 4 (SP4)')
or   UPPER(KBDescription) like UPPER('%Service Pack 3 (SP3)')
or   UPPER(KBDescription) like UPPER('%Service Pack 2 (SP2)')
or   UPPER(KBDescription) like UPPER('%Service Pack 1 (SP1)')

update  dbo.tblBuildList
set   CU = 1 
where  UPPER(KBDescription) like UPPER('%Cumulative update 33 (CU33)%')
or     UPPER(KBDescription) like UPPER('%Cumulative update 32 (CU32)%')
or     UPPER(KBDescription) like UPPER('%Cumulative update 31 (CU31)%')
or     UPPER(KBDescription) like UPPER('%Cumulative update 30 (CU30)%')
or     UPPER(KBDescription) like UPPER('%Cumulative update 29 (CU29)%')
or     UPPER(KBDescription) like UPPER('%Cumulative update 28 (CU28)%')
or     UPPER(KBDescription) like UPPER('%Cumulative update 27 (CU27)%')
or     UPPER(KBDescription) like UPPER('%Cumulative update 26 (CU26)%')
or     UPPER(KBDescription) like UPPER('%Cumulative update 25 (CU25)%')
or     UPPER(KBDescription) like UPPER('%Cumulative update 24 (CU24)%')
or     UPPER(KBDescription) like UPPER('%Cumulative update 23 (CU23)%')
or     UPPER(KBDescription) like UPPER('%Cumulative update 22 (CU22)%')
or     UPPER(KBDescription) like UPPER('%Cumulative update 21 (CU21)%')
or     UPPER(KBDescription) like UPPER('%Cumulative update 20 (CU20)%')
or   UPPER(KBDescription) like UPPER('%Cumulative update 19 (CU19)%')
or   UPPER(KBDescription) like UPPER('%Cumulative update 18 (CU18)%')
or   UPPER(KBDescription) like UPPER('%Cumulative update 17 (CU17)%')
or   UPPER(KBDescription) like UPPER('%Cumulative update 16 (CU16)%')
or   UPPER(KBDescription) like UPPER('%Cumulative update 15 (CU15)%')
or   UPPER(KBDescription) like UPPER('%Cumulative update 14 (CU14)%')
or   UPPER(KBDescription) like UPPER('%Cumulative update 13 (CU13)%')
or   UPPER(KBDescription) like UPPER('%Cumulative update 12 (CU12)%')
or   UPPER(KBDescription) like UPPER('%Cumulative update 11 (CU11)%')
or   UPPER(KBDescription) like UPPER('%Cumulative update 10 (CU10)%')
or   UPPER(KBDescription) like UPPER('%Cumulative update 9 (CU9)%')
or   UPPER(KBDescription) like UPPER('%Cumulative update 8 (CU8)%')
or   UPPER(KBDescription) like UPPER('%Cumulative update 7 (CU7)%')
or   UPPER(KBDescription) like UPPER('%Cumulative update 6 (CU6)%')
or   UPPER(KBDescription) like UPPER('%Cumulative update 5 (CU5)%')
or   UPPER(KBDescription) like UPPER('%Cumulative update 4 (CU4)%')
or   UPPER(KBDescription) like UPPER('%Cumulative update 3 (CU3)%')
or   UPPER(KBDescription) like UPPER('%Cumulative update 2 (CU2)%')
or   UPPER(KBDescription) like UPPER('%Cumulative update 1 (CU1)%')

update  dbo.tblBuildList
set   CU = 1 
where  UPPER(KBDescription) like UPPER('%Cumulative update package 33 (CU33)%')
or   UPPER(KBDescription) like UPPER('%Cumulative update package 32 (CU32)%')
or   UPPER(KBDescription) like UPPER('%Cumulative update package 31 (CU31)%')
or   UPPER(KBDescription) like UPPER('%Cumulative update package 30 (CU30)%')
or   UPPER(KBDescription) like UPPER('%Cumulative update package 29 (CU29)%')
or   UPPER(KBDescription) like UPPER('%Cumulative update package 28 (CU28)%')
or   UPPER(KBDescription) like UPPER('%Cumulative update package 27 (CU27)%')
or   UPPER(KBDescription) like UPPER('%Cumulative update package 26 (CU26)%')
or   UPPER(KBDescription) like UPPER('%Cumulative update package 25 (CU25)%')
or   UPPER(KBDescription) like UPPER('%Cumulative update package 24 (CU24)%')
or   UPPER(KBDescription) like UPPER('%Cumulative update package 23 (CU23)%')
or   UPPER(KBDescription) like UPPER('%Cumulative update package 22 (CU22)%')
or   UPPER(KBDescription) like UPPER('%Cumulative update package 21 (CU21)%')
or   UPPER(KBDescription) like UPPER('%Cumulative update package 20 (CU20)%')
or   UPPER(KBDescription) like UPPER('%Cumulative update package 19 (CU19)%')
or   UPPER(KBDescription) like UPPER('%Cumulative update package 18 (CU18)%')
or   UPPER(KBDescription) like UPPER('%Cumulative update package 17 (CU17)%')
or   UPPER(KBDescription) like UPPER('%Cumulative update package 16 (CU16)%')
or   UPPER(KBDescription) like UPPER('%Cumulative update package 15 (CU15)%')
or   UPPER(KBDescription) like UPPER('%Cumulative update package 14 (CU14)%')
or   UPPER(KBDescription) like UPPER('%Cumulative update package 13 (CU13)%')
or   UPPER(KBDescription) like UPPER('%Cumulative update package 12 (CU12)%')
or   UPPER(KBDescription) like UPPER('%Cumulative update package 11 (CU11)%')
or   UPPER(KBDescription) like UPPER('%Cumulative update package 10 (CU10)%')
or   UPPER(KBDescription) like UPPER('%Cumulative update package 9 (CU9)%')
or   UPPER(KBDescription) like UPPER('%Cumulative update package 8 (CU8)%')
or   UPPER(KBDescription) like UPPER('%Cumulative update package 7 (CU7)%')
or   UPPER(KBDescription) like UPPER('%Cumulative update package 6 (CU6)%')
or   UPPER(KBDescription) like UPPER('%Cumulative update package 5 (CU5)%')
or   UPPER(KBDescription) like UPPER('%Cumulative update package 4 (CU4)%')
or   UPPER(KBDescription) like UPPER('%Cumulative update package 3 (CU3)%')
or   UPPER(KBDescription) like UPPER('%Cumulative update package 2 (CU2)%')
or   UPPER(KBDescription) like UPPER('%Cumulative update package 1 (CU1)%')

update  dbo.tblBuildList
set   HF = 1 
where   UPPER(KBDescription) like UPPER('%Hotfix%')

update  dbo.tblBuildList
set   CTP = 1 
where   UPPER(KBDescription) like UPPER('%Community Technology Preview%')

update  dbo.tblBuildList
set   CTP = 1 
where   UPPER(KBDescription) like UPPER('%Release Candidate%')

--------------------------------------------------------------------------------
-- Done
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
-- Time to select what you need, or use the table for automated patch checks
--------------------------------------------------------------------------------

---- I.E. The latest SP and CU per SQL version
--select  *
--from   dbo.tblBuildList
--where   LatestSP = 1 
--or    LatestCU = 1
--ORDER BY Major desc
--   ,Minor desc
--   ,BuildNr desc
--   ,Revision desc

This reply was modified 3 years, 7 months ago by Theo Ekelmans.
This reply was modified 3 years, 7 months ago by Theo Ekelmans.
This reply was modified 3 years, 7 months ago by Theo Ekelmans.

Darryl Wilson Mr or Mrs. 500 Points: 526 More actions · Answer 4

Hi,

I downloaded the files and tried running them but they're causing errors. Is there any chance you could reformat them. there are commands following on from each other no line breaks. this includes the 1st line Reason: like '%0 and BEGINPRINT

Darryl Wilson
darrylw99@hotmail.com

Theo Ekelmans SSCarpal Tunnel Points: 4592 More actions · Answer 5

Some small changes to the script guys.

Please download the new version here: http://www.ekelmans.com/FTP/Web/ImportSqlserverbuildsBlogspotCom.sql

christianl Newbie Points: 3 More actions · Answer 6

Hi,

It seems like the latest change in the script made the ProductVersion for SQL 2008 R2 to null.

Tim Roberts-250428 SSC Veteran Points: 244 More actions · Answer 7

Hello Theo. This is a great solution that keeps me up to date with what patches are available. It stopped working for me just over a week ago on 1st July 2021. I have tried troubleshooting, but am a bit lost why it is no longer working. I setup the solution again from scratch using the scripts you kindly provide. I can see data is being captured into the out.html file using the wget.exe, but it's not getting imported into the database. The TSQL script runs without error, but no data appears to be inserted into the tables. I'm assuming the solution must still work as I can see the version on your website is up to date (http://sqlbuilds.ekelmans.com/). Just wondering if this is a known issue and whether there is something I can do to fix it. Any tips or ideas on what I might be doing wrong would be greatly appreciated. Thanks.

Theo Ekelmans SSCarpal Tunnel Points: 4592 More actions · Answer 8

Hi Tim 🙂

Just checking... you did download the latest version of the script ?

http://www.ekelmans.com/FTP/Web/ImportSqlserverbuildsBlogspotCom.sql

This reply was modified 2 years, 10 months ago by Theo Ekelmans.

tomek.balicki SSC Rookie Points: 41 More actions · Answer 9

Hello Theo,

I had also come across issues recently. I had to remove some lines from out.html to get it working.

First the sp_OAMethod was only reading 75 lines into @html. I deleted line 76 from the out.html to get it working.

76 line starts with "<nav class="menu"><span class="menu-icon" onclick="if(menuUl.style.display=='')..."

After that the file got read succesfully but there were issues with CU11 for SQL 2019 and CU25 for SQL 2017.

Both got some string data in release date column.

- CU25 for SQL 2017 is not yet released and its release data is a string - "July 2021" - i have dropped it from the out.html

- CU11 for SQL 2019 has some additional code to get "*new" text in the release date field - i have deleted it

After that all the records got inserted, but I'm looking forward to have it done automatically in your brilliant solution!

SqlServerBuilds Grasshopper Points: 14 More actions · Answer 10

Hello,

I'm here again. Again, I kindly recommend you to use a public Google document as your data source, as described here:

How to get SqlServerBuilds data programmatically?

https://sqlserverbuilds.blogspot.com/2019/08/how-to-get-data-programmatically.html

For simplicity, for example CSV format.

We maintain the document carefully, its structure does not change. None of the above parsing issues occur there.

—SqlServerBuilds site admin

This reply was modified 2 years, 10 months ago by SqlServerBuilds.
This reply was modified 2 years, 10 months ago by SqlServerBuilds.

Theo Ekelmans SSCarpal Tunnel Points: 4592 More actions · Answer 11

Hi Guys,

Like the admin of SqlServerBuilds site said... There is a now better way than screenscraping to get the data from his awesome site ! 🙂

The code below is just a quick first version to import the SqlServerBuild TSV export into a SQL table, which i will adapt to work with the tables of my older versions of code, which will save you guys a bit of time.

This version uses powershell for the url download instead of the external tool WGET, and uses BCP for file access to the SQL default log folder.

@ the admin of SqlServerBuilds site ... there still is one tiny parsing error 🙂 (just kidding dude... it depends on the import tool, BCP needed a CRLF at the end of each line)

USE [ServerInfo]

set nocount on 

declare @Cmd varchar(1000) 
declare @url varchar(1000) 
declare @Path varchar(1000)
declare @FileName varchar (1024)
declare @FmtFileName varchar (1024)
declare @sql nvarchar(4000)
declare @txt varchar(4000)

-----------------------------------------------------------------------------------------------------
-- Update the url if the TSV export link should ever change,
-----------------------------------------------------------------------------------------------------
set @url = 'https://docs.google.com/spreadsheets/d/16Ymdz80xlCzb6CwRFVokwo0onkofVYFoSkc7mYe6pgw/export?gid=0&format=tsv'

-----------------------------------------------------------------------------------------------------
-- Table section
-----------------------------------------------------------------------------------------------------
IF EXISTS(SELECT * FROM sys.tables WHERE SCHEMA_NAME(schema_id) LIKE 'dbo' AND name like '_tmp')  
   drop table dbo._tmp

IF NOT EXISTS(SELECT * FROM sys.tables WHERE SCHEMA_NAME(schema_id) LIKE 'dbo' AND name like 'tblBuildListImportCsv')  
CREATE TABLE [dbo].[tblBuildListImportCsv](
SQLServer [varchar](1000) NULL,
Version [varchar](1000) NULL,
Build [varchar](1000) NULL,
FileVersion [varchar](1000) NULL,
Description [varchar](1000) NULL,
Link [varchar](1000) NULL,
ReleaseDate [varchar](1000) NULL,
SP [varchar](1000) NULL,
CU [varchar](1000) NULL,
HF [varchar](1000) NULL,
RTM [varchar](1000) NULL,
CTP [varchar](1000) NULL,
New [varchar](1000) NULL,
Withdrawn [varchar](1000) NULL
) ON [PRIMARY]

-----------------------------------------------------------------------------------------------------
-- Get SQL errorlog path (a convenient place to store bcp files)
-----------------------------------------------------------------------------------------------------
set @Path = (select substring(cast(SERVERPROPERTY('ErrorLogFileName') as varchar(256)), 1 , len(cast(SERVERPROPERTY('ErrorLogFileName') as varchar(256))) - 8))

-----------------------------------------------------------------------------------------------------
-- Create .FMT file in SQL log folder
-----------------------------------------------------------------------------------------------------
set @FmtFileName  = @Path+'out.fmt'

--Delete the old file
set @Cmd = 'del "'+@FmtFileName +'"'
exec xp_cmdshell @Cmd, no_output 

--insert Format file for BCP into temp table
set @txt='14.0
14
1       SQLCHAR             0       1000    "\t"     1     SQLServer                  Latin1_General_CI_AS
2       SQLCHAR             0       1000    "\t"     2     Version                    Latin1_General_CI_AS
3       SQLCHAR             0       1000    "\t"     3     Build                      Latin1_General_CI_AS
4       SQLCHAR             0       1000    "\t"     4     FileVersion                Latin1_General_CI_AS
5       SQLCHAR             0       1000    "\t"     5     Description                Latin1_General_CI_AS
6       SQLCHAR             0       1000    "\t"     6     Link                       Latin1_General_CI_AS
7       SQLCHAR             0       1000    "\t"     7     ReleaseDate                Latin1_General_CI_AS
8       SQLCHAR             0       1000    "\t"     8     SP                         Latin1_General_CI_AS
9       SQLCHAR             0       1000    "\t"     9     CU                         Latin1_General_CI_AS
10      SQLCHAR             0       1000    "\t"     10    HF                         Latin1_General_CI_AS
11      SQLCHAR             0       1000    "\t"     11    RTM                        Latin1_General_CI_AS
12      SQLCHAR             0       1000    "\t"     12    CTP                        Latin1_General_CI_AS
13      SQLCHAR             0       1000    "\t"     13    New                        Latin1_General_CI_AS
14      SQLCHAR             0       1000    "\r\n"   14    Withdrawn                  Latin1_General_CI_AS
'

select @txt as txt
into dbo._tmp

--Export Format file to log folder for future use
SET @Cmd ='bcp "select * from ServerInfo.dbo._tmp" queryout "'+@FmtFileName +'" -c -T'
exec xp_cmdshell @Cmd, no_output 

--Clean up temp table
drop table dbo._tmp

-----------------------------------------------------------------------------------------------------
-- Import SqlServerBuilds google file export to csv file in SQL log folder
-----------------------------------------------------------------------------------------------------
set @FileName = @Path+'out.csv'

set @Cmd = 'del "'+@FileName+'"'
exec xp_cmdshell @Cmd, no_output 

-- Get the SqlBuilds tab seperated file info the log folder as a csv file
set @Cmd = 'powershell.exe -Command "Add-Type -Assembly System.Web; Invoke-WebRequest '''+@url+''' -OutFile '''+@FileName+'''" '
exec xp_cmdshell @Cmd, no_output 

--Add the missing crlf at the end of the file
SET @Cmd = 'cmd.exe /C echo.>>"' + @FileName + '"';
EXEC xp_cmdshell @Cmd, no_output;

-----------------------------------------------------------------------------------------------------
-- Import csv file (skip header) in SQL log folder to table 
-----------------------------------------------------------------------------------------------------
truncate table dbo.tblBuildListImportCsv 

set @Cmd = 'bcp ServerInfo.dbo.tblBuildListImportCsv in "'+@FileName+'" -f "'+@FmtFileName+'" -T -F2'
exec xp_cmdshell @Cmd, no_output 

select * from dbo.tblBuildListImportCsv

Enjoy ... Theo 🙂

This reply was modified 2 years, 10 months ago by Theo Ekelmans.
This reply was modified 2 years, 10 months ago by Theo Ekelmans.
This reply was modified 2 years, 10 months ago by Theo Ekelmans.

SqlServerBuilds Grasshopper Points: 14 More actions · Answer 12

CRLF can be added to the end with these 2 lines:

SET @Cmd = 'cmd.exe /C echo.>>"' + @FileName + '"';
EXEC xp_cmdshell @Cmd, no_output;

—SqlServerBuilds site admin

This reply was modified 2 years, 10 months ago by SqlServerBuilds.
This reply was modified 2 years, 10 months ago by SqlServerBuilds.

Theo Ekelmans SSCarpal Tunnel Points: 4592 More actions · Answer 13

Yup,

That fixes the missing last line.

Thanks 🙂

This reply was modified 2 years, 10 months ago by Theo Ekelmans.

Tim Roberts-250428 SSC Veteran Points: 244 More actions · Answer 14

Many thanks Theo and everyone else for providing a solution. Much appreciated!