Download SQL Patch Info

  • And as promised, here is the new script that will download SqlServerBuild's google TSV file and import it into the original data model of this article:

    Use ServerInfo
    GO
    ALTER PROCEDURE [dbo].[spSqlBuildListUpdate] AS
    /******************************************************************************************
    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: 2.0
    Author: Theo Ekelmans
    Email: theo@ekelmans.com
    Date: 2021-07-23
    Change: Total rewrite based upon sqlserverbuilds.blogspot.com google file

    ******************************************************************************************/
    set nocount on

    declare @Cmd varchar(1000)
    declare @SQL Nvarchar(1000)
    declare @Path varchar(1000)
    declare @FileName varchar(1000)
    declare @url varchar(1250)
    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))

    -----------------------------------------------------------------------------------------------------
    -- 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 ServerInfo.dbo.tblBuildListImportCsv

    set @SQL = 'BULK INSERT dbo.tblBuildListImportCsv FROM '''+@FileName+''' WITH (DATAFILETYPE = ''char'',FIELDTERMINATOR = ''\t'',ROWTERMINATOR = ''\n'',FIRSTROW = 2)'
    exec sp_executesql @SQL

    --debug-- select * from ServerInfo.dbo.tblBuildListImportCsv


    --------------------------------------------------------------------------------
    -- Insert new Build records in tblBuildList
    --------------------------------------------------------------------------------
    INSERT INTO dbo.tblBuildList
    (Version
    ,ProductVersion
    ,Build
    ,FileVersion
    ,KBDescription
    ,URL
    ,ReleaseDate
    ,SP
    ,CU
    ,HF
    ,RTM
    ,CTP
    ,LatestSP
    ,LatestCU
    ,New
    ,Comment)
    select i.SQLServer
    ,i.Version
    ,i.Build
    ,coalesce(i.FileVersion, '')
    ,i.Description
    ,i.Link
    ,coalesce(i.ReleaseDate, '')
    ,case when i.SP is null then 0 when i.SP= 'TRUE' then 1 else 0 end
    ,case when i.CU is null then 0 when i.CU= 'TRUE' then 1 else 0 end
    ,case when i.HF is null then 0 when i.HF= 'TRUE' then 1 else 0 end
    ,case when i.RTM is null then 0 when i.RTM= 'TRUE' then 1 else 0 end
    ,case when i.CTP is null then 0 when i.CTP= 'TRUE' then 1 else 0 end
    ,0
    ,0
    ,case when i.New is null then 0 when i.New= 'TRUE' then 1 else 0 end
    ,case when i.Withdrawn is null then ''else 'Withdrawn' end
    fromdbo.tblBuildListImportCsv i
    left join dbo.tblBuildList l on i.Build = l.Build
    wherel.ID is null

    --------------------------------------------------------------------------------
    -- Send email if new records are found
    --------------------------------------------------------------------------------
    if @@ROWCOUNT > 0
    begin

    declare @mailbody varchar(1000) = 'See https://as-ordsql001.azurewebsites.net/SqlBuilds4.aspx for details'

    execute msdb.dbo.sp_send_dbmail
    @profile_name = 'Ordina'
    ,@recipients = 'mssql@ordina.nl'
    ,@subject = 'New SQL patches detected'
    ,@body = @mailbody
    ,@body_format = 'HTML' -- or TEXT
    ,@importance = 'Normal' --Low Normal High
    ,@sensitivity = 'Normal' --Normal Personal Private Confidential
    end

    --------------------------------------------------------------------------------
    -- Update info (Because it could change)
    --------------------------------------------------------------------------------
    updatedbo.tblBuildList
    set Version = i.Version
    ,ProductVersion = i.ProductVersion
    ,FileVersion = coalesce(i.FileVersion, '')
    ,KBDescription = i.KBDescription
    ,URL = i.URL
    ,ReleaseDate = coalesce(i.ReleaseDate, '')
    ,SP = i.SP
    ,CU = i.CU
    ,HF = i.HF
    ,RTM = i.RTM
    ,CTP = i.CTP
    ,New = i.New
    ,Comment = i.Comment --as Cmt
    FROM(select SQLServeras Version
    ,Versionas ProductVersion
    ,Buildas Build
    ,FileVersionas FileVersion
    ,Descriptionas KBDescription
    ,Link as URL
    ,ReleaseDateas ReleaseDate
    ,case when SP is null then 0 when SP= 'TRUE' then 1 else 0 end as SP
    ,case when CU is null then 0 when CU= 'TRUE' then 1 else 0 end as CU
    ,case when HF is null then 0 when HF= 'TRUE' then 1 else 0 end as HF
    ,case when RTM is null then 0 when RTM= 'TRUE' then 1 else 0 end as RTM
    ,case when CTP is null then 0 when CTP= 'TRUE' then 1 else 0 end as CTP
    ,case when New is null then 0 when New= 'TRUE' then 1 else 0 end as New
    ,case when Withdrawn is null then '' else 'Withdrawn' end as Comment
    from dbo.tblBuildListImportCsv ) i
    left join dbo.tblBuildList l on i.Build = l.Build

    ------------------------------------------------------------------------------------
    ------ Update flags latest SP and CU flags
    ------------------------------------------------------------------------------------
    update [dbo].[tblBuildList]
    set LatestCU = 1
    where Build in (SELECT max([Build])
    FROM [dbo].[tblBuildList]
    where CU = 1
    group by Version)

    update [dbo].[tblBuildList]
    set LatestSP = 1
    where Build in (SELECT max([Build])
    FROM [dbo].[tblBuildList]
    where SP = 1
    group by Version)

    --------------------------------------------------------------------------------
    -- 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
    */

  • Fantastic, thanks Theo!

  • First of all thanks Theo for putting your efforts !

    I have automated SQL patching in our environment and I had been using data from the tables tblBuildList and tblBuildListImport from over 2 yrs now, until it broke last week and now even with latest script, it does not seems to populate records in either tables.

    We cannot use google file as a source as any external file share location is blocked in our organization. I am sure all big companies have restrictions in place to access files from external file source (like google, AWS S3 etc.)

    I would really appreciate if you fix the old script as well. Thanks

  • Hi Sajal,

    I have spoken with the author of the https://sqlserverbuilds.blogspot.com/website, and we agreed upon stopping with screenscraping, hence the move to sqlserverbuilds's google docs.

    Does your WGET still have access to websites? In that case, you might want to download JSON or XML from my site: http://sqlbuilds.ekelmans.com/, and simple "upsert" that data into tblBuildlist.

    Let me know if this is an option for you.

    grtz, Theo

     

     

     

    • This reply was modified 9 months, 2 weeks ago by  Theo Ekelmans.
  • I got a few errors off the bat, about accessing sp_cmdshell, so these are the commands i needed to run:

    EXEC sp_configure 'show advanced options', 1

    GO

    -- To update the currently configured values for sp_configure

    RECONFIGURE WITH OVERRIDE

    GO

    -- Now, enable the feature.

    EXEC sp_configure 'xp_cmdshell', 1

    GO

    -- To update the currently configured values for sp_configure

    RECONFIGURE WITH OVERRIDE

    GO

    sp_configure 'show advanced options', 1

    GO

    RECONFIGURE;

    GO

    sp_configure 'Ole Automation Procedures', 1

    GO

    RECONFIGURE;

    GO

    sp_configure 'show advanced options', 1

    GO

    RECONFIGURE;

    Darryl Wilson
    darrylw99@hotmail.com

  • Hi Theo

    I was hoping this would automate the download and installation of the latest SP/CU. Is this not the function of your script? I cant see an actual download filename just a url to a webpage.

     

    Thanks

    Darryl

    Darryl Wilson
    darrylw99@hotmail.com

  • Hi Darryl,

    No, the purpose of this script is to update a table on one of your central SQL management servers with the list of all available patches for all SQL versions and provide you with their download links. You can then check all your SQL instances @@version info against this table to see if they are missing any patches.

    After you know which servers need to be updated you can use your tool of choice like WSUS or DBATOOLS (see Update-DbaInstance) to automate the distribution of the SQL patches.

    Grtz,

    Theo.

     

    • This reply was modified 9 months, 2 weeks ago by  Theo Ekelmans.
  • set nocount on;
    DECLARE @Object AS INT;
    DECLARE @json1 AS TABLE(Json_Table NVARCHAR(MAX));
    DECLARE @jsondata NVARCHAR(MAX);
    DECLARE @json AS NVARCHAR(MAX);


    EXEC sp_OACreate
    'WinHttp.WinHttpRequest.5.1', @Object OUT;
    EXEC sp_OAMethod @Object,'open',NULL,'get','http://sqlbuilds.ekelmans.com/sqlbuilds.json','false';
    EXEC sp_OAMethod @Object,'send';
    EXEC sp_OAMethod @Object,'responseText', @json OUTPUT;

    INSERT INTO @json1(Json_Table)

    EXEC sp_OAGetProperty @Object,'responseText';

    SET @jsondata =(SELECT * FROM @json1);

    IF(ISJSON(@jsondata) > 0)
    BEGIN
    PRINT 'Valid json';
    SELECT [Version],
    [ProductVersion],
    Build,
    Case when URL='' then 'Check URL for Kb detail'
    else RIGHT(URL,charindex('/',reverse(URL),1)-1)end as[KB],
    KBDescription,
    URL,
    [ReleaseDate],
    [SP],
    [CU],
    [RTM],
    [CTP],
    [LatestSP],
    [LatestCU],
    [Major],
    [Minor],
    [BuildNr],
    [Revision]

    FROM OPENJSON(@jsondata,
    '$.SqlBuildList') WITH(Version VARCHAR(50) '$.Version', ProductVersion VARCHAR(50) '$.ProductVersion', Build VARCHAR(50) '$.Build', KBDescription VARCHAR(200) '$.KBDescription', URL NVARCHAR(500) '$.URL', ReleaseDate DATE '$.ReleaseDate', SP INT '$.SP', CU INT '$.CU', RTM INT '$.RTM', CTP INT '$.CTP', LatestSP INT '$.LatestSP', LatestCU INT '$.LatestCU', Major INT '$.Major', Minor INT '$.Minor', BuildNr INT '$.BuildNr', Revision INT '$.Revision') order by ReleaseDate desc
    END;
    ELSE
    BEGIN
    PRINT 'Invalid JSON';
    END;



    Thanks Theo. I am now referring the Json from your portal http://sqlbuilds.ekelmans.com/sqlbuilds.json , which fortunately is not blocked 🙂

    I am hoping it is automated and gets updated on new patch release.

    Thanks again for all your hard work !

     

    For others, attaching a script which I wrote to parse the data which uses json file from http://sqlbuilds.ekelmans.com/sqlbuilds.json

    You may wrap it up in a procedure and setup a job to run every 2 hrs and put the result in a SQL table to refer for patch automation.

     

    • This reply was modified 9 months, 1 week ago by  sajal19866.
    • This reply was modified 9 months, 1 week ago by  sajal19866.
    • This reply was modified 9 months, 1 week ago by  sajal19866.
  • One small request- if you could also include KB number in your Json as I had to strip KB from URL to list it in separate field, which is not an ideal way I know 🙂

    KB# is really an important field for patch automation if using dbatool Powershell command Update-DbaInstance, as it accepts kb as a parameter for CU/Security patch.

     

    Eg: Update-DbaInstance -ComputerName SQL1 -KB 123456 -Restart -Path \\network\share -Confirm:$false

  • Hi Sajal,

    I have added a new Json file to my website with the KB column you wanted:)

    grtz, Theo.

    set nocount on;
    DECLARE @Object AS INT;
    DECLARE @json1 AS TABLE(Json_Table NVARCHAR(MAX));
    DECLARE @jsondata NVARCHAR(MAX);
    DECLARE @json AS NVARCHAR(MAX);


    EXEC sp_OACreate
    'WinHttp.WinHttpRequest.5.1', @Object OUT;
    EXEC sp_OAMethod @Object,'open',NULL,'get','http://sqlbuilds.ekelmans.com/sqlbuildsKb.json','false';
    EXEC sp_OAMethod @Object,'send';
    EXEC sp_OAMethod @Object,'responseText', @json OUTPUT;

    INSERT INTO @json1(Json_Table)

    EXEC sp_OAGetProperty @Object,'responseText';

    SET @jsondata =(SELECT * FROM @json1);


    IF(ISJSON(@jsondata) > 0)
    BEGIN
    PRINT 'Valid json';
    SELECT [Version],
    [ProductVersion],
    Build,
    [KB],
    KBDescription,
    URL,
    [ReleaseDate],
    [SP],
    [CU],
    [RTM],
    [CTP],
    [LatestSP],
    [LatestCU],
    [Major],
    [Minor],
    [BuildNr],
    [Revision]
    FROM OPENJSON(@jsondata, '$.SqlBuildKbList') WITH(Version VARCHAR(50) '$.Version', ProductVersion VARCHAR(50) '$.ProductVersion', Build VARCHAR(50) '$.Build', KB VARCHAR(50) '$.KB', KBDescription VARCHAR(200) '$.KBDescription', URL NVARCHAR(500) '$.URL', ReleaseDate DATE '$.ReleaseDate', SP INT '$.SP', CU INT '$.CU', RTM INT '$.RTM', CTP INT '$.CTP', LatestSP INT '$.LatestSP', LatestCU INT '$.LatestCU', Major INT '$.Major', Minor INT '$.Minor', BuildNr INT '$.BuildNr', Revision INT '$.Revision') order by ReleaseDate desc
    END;
    ELSE
    BEGIN
    PRINT 'Invalid JSON';
    END;

    print @jsondata
  • Thank you so much. You are a saviour !! 🙂

  • Theo Ekelmans,

    Thank you so much Sir for creating this automated process. After all these years not sure why MS has not created a simple process for us DBA to consume to pull latest information. We DBA are very thankful and appreciate your effort.

    Sajal,

    Thank you so much for creating script which we can consume easily.

    Thank you,

    HP

  • Hi Theo,

    Is the JSON not being updated now ? 🙁

    I don't see information about latest SQL patch for 2017 (CU 26) and SQL 2019 (CU13)

     

    Regards,

    Sajal Bagchi

  • do you have a version of this where you start from scratch. some of the tables that are referenced in the above script aren't created yet. it tblBuildList

  • Hi Theo, could you please confirm if the JSON file in your portal is not being updated anymore ?

Viewing 15 posts - 61 through 74 (of 74 total)

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