Download SQL Patch Info

  • 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 2 years, 5 months ago by  Theo Ekelmans.
  • Hello,

    I can see that the โ€œCU flagโ€ for CU21 and CU22 is missing for SQL 2017, can you add them?

    /Christian

  • 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
  • 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

  • Some small changes to the script guys.

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

  • Hi,

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

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

  • 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.

  • Hi Tim ๐Ÿ™‚

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

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

  • 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!

  • 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

  • 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 ๐Ÿ™‚

  • 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

  • Yup,

    That fixes the missing last line.

    Thanks ๐Ÿ™‚

    <updating script>

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

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

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