July 23, 2021 at 4:30 pm
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
*/
July 26, 2021 at 9:00 am
Fantastic, thanks Theo!
August 10, 2021 at 9:04 pm
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
August 11, 2021 at 7:51 am
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
August 11, 2021 at 1:54 pm
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
August 11, 2021 at 2:24 pm
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
August 11, 2021 at 8:37 pm
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.
August 12, 2021 at 9:23 pm
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.
August 12, 2021 at 9:29 pm
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
August 13, 2021 at 8:30 pm
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
August 13, 2021 at 9:39 pm
Thank you so much. You are a saviour !! 🙂
August 17, 2021 at 8:19 pm
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
October 8, 2021 at 8:48 pm
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
January 13, 2022 at 7:29 pm
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
January 18, 2022 at 5:25 pm
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