Speak guys!
In this article I want to bring you a solution I created yesterday to include in a checklist I'm developing, which allows me to identify the latest version of Service Pack (SP) and Cumulative Update (CU) for the version of SQL Server where I am running this code.
To identify the latest updates for each version, I will use the site https://sqlserverbuilds.blogspot.com/, which is always up to date with the latest SQL Server updates (could also be the https://buildnumbers.wordpress.com/sqlserver/).
To extract page data to SQL Server, I will use the OLE Automation, which has already published several articles explaining how to perform WebScraping of WEB pages and API's using this resource.
After retrieving the page's HTML source code, I need to identify the table that contains the data according to the version I'm looking for and also need to perform some treatment on the returned code to correct some inconsistencies:
- Conversion of HTML entities (ex:)
- Attributes of tags without quotes (eg class = sp instead of class = ”sp” = invalid HTML)
- Open and not closed tags (ex: Column1 Column2 instead of Column1 Column2 )
This turns out to be the hardest part of this script, because if these code inconsistencies are not remedied, SQL Server returns me an error when trying to convert XHTML to XML.
After performing all of these treatments and converting the XHTML code to XML, I apply XQuery commands to identify the first row of the respective SQL Server version table and return to SP
Source code
After a brief summary of what I needed to do in order to accomplish this task, I will make available below the source code of the created solution, which can be used with a simple “F5”.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 |
IF (OBJECT_ID('dbo.stpRetorna_Ultima_Versao_SQL') IS NULL) EXEC('CREATE PROCEDURE dbo.stpRetorna_Ultima_Versao_SQL AS SELECT 1') GO ALTER PROCEDURE dbo.stpRetorna_Ultima_Versao_SQL ( @Versao VARCHAR(20) = NULL ) AS BEGIN -------------------------------------------------------------------------------- -- Habilitando o OLE Automation (Se não estiver ativado) -------------------------------------------------------------------------------- DECLARE @Fl_Ole_Automation_Ativado BIT = (SELECT (CASE WHEN CAST([value] AS VARCHAR(MAX)) = '1' THEN 1 ELSE 0 END) FROM sys.configurations WHERE [name] = 'Ole Automation Procedures') IF (@Fl_Ole_Automation_Ativado = 0) BEGIN EXECUTE sp_configure 'show advanced options', 1 RECONFIGURE WITH OVERRIDE EXEC sp_configure 'Ole Automation Procedures', 1 RECONFIGURE WITH OVERRIDE END DECLARE @obj INT, @Url VARCHAR(8000), @xml VARCHAR(MAX), @resposta VARCHAR(MAX) SET @Url = 'http://sqlserverbuilds.blogspot.com/' EXEC sys.sp_OACreate 'MSXML2.ServerXMLHTTP', @obj OUT EXEC sys.sp_OAMethod @obj, 'open', NULL, 'GET', @Url, false EXEC sys.sp_OAMethod @obj, 'send' DECLARE @xml_versao_sql TABLE ( Ds_Dados VARCHAR(MAX) ) INSERT INTO @xml_versao_sql(Ds_Dados) EXEC sys.sp_OAGetProperty @obj, 'responseText' --, @resposta OUT EXEC sys.sp_OADestroy @obj -------------------------------------------------------------------------------- -- Desativando o OLE Automation (Se não estava habilitado antes) -------------------------------------------------------------------------------- IF (@Fl_Ole_Automation_Ativado = 0) BEGIN EXEC sp_configure 'Ole Automation Procedures', 0 RECONFIGURE WITH OVERRIDE EXECUTE sp_configure 'show advanced options', 0 RECONFIGURE WITH OVERRIDE END DECLARE @Versao_SQL_Build VARCHAR(20) IF (@Versao IS NOT NULL) SET @Versao_SQL_Build = @Versao ELSE BEGIN SET @Versao_SQL_Build = (CASE LEFT(CONVERT(VARCHAR, SERVERPROPERTY('ProductVersion')), 2) WHEN '8.' THEN '2000' WHEN '9.' THEN '2005' WHEN '10' THEN ( CASE WHEN LEFT(CONVERT(VARCHAR, SERVERPROPERTY('ProductVersion')), 4) = '10.5' THEN '2008 R2' WHEN LEFT(CONVERT(VARCHAR, SERVERPROPERTY('ProductVersion')), 4) = '10.0' THEN '2008' END) WHEN '11' THEN '2012' WHEN '12' THEN '2014' WHEN '13' THEN '2016' WHEN '14' THEN '2017' WHEN '15' THEN '2019' ELSE '2019' END) END SELECT TOP 1 @resposta = Ds_Dados FROM @xml_versao_sql SET @xml = @resposta COLLATE SQL_Latin1_General_CP1251_CS_AS DECLARE @PosicaoInicialVersao INT, @PosicaoFinalVersao INT, @ExpressaoBuscar VARCHAR(100) = 'Microsoft SQL Server ' + @Versao_SQL_Build + ' Builds', @RetornoTabela VARCHAR(MAX), @dadosXML XML SET @PosicaoInicialVersao = CHARINDEX(@ExpressaoBuscar, @xml) + LEN(@ExpressaoBuscar) + 6 SET @PosicaoFinalVersao = CHARINDEX('</table>', @xml, @PosicaoInicialVersao) SET @RetornoTabela = SUBSTRING(@xml, @PosicaoInicialVersao, @PosicaoFinalVersao - @PosicaoInicialVersao + 8) -- Corrigindo classes sem aspas duplas ("") SET @RetornoTabela = REPLACE(@RetornoTabela, ' border=1 cellpadding=4 cellspacing=0 bordercolor="#CCCCCC" style="border-collapse:collapse"', '') SET @RetornoTabela = REPLACE(@RetornoTabela, ' target=_blank rel=nofollow', ' target="_blank" rel="nofollow"') SET @RetornoTabela = REPLACE(@RetornoTabela, ' class=h', '') SET @RetornoTabela = REPLACE(@RetornoTabela, ' class=lsp', '') SET @RetornoTabela = REPLACE(@RetornoTabela, ' class=cu', '') SET @RetornoTabela = REPLACE(@RetornoTabela, ' class=sp', '') SET @RetornoTabela = REPLACE(@RetornoTabela, ' class=rtm', '') SET @RetornoTabela = REPLACE(@RetornoTabela, ' width=580', '') SET @RetornoTabela = REPLACE(@RetornoTabela, ' width=125', '') SET @RetornoTabela = REPLACE(@RetornoTabela, ' class=lcu', '') SET @RetornoTabela = REPLACE(@RetornoTabela, ' class=cve', '') SET @RetornoTabela = REPLACE(@RetornoTabela, ' class=lrtm', '') SET @RetornoTabela = REPLACE(@RetornoTabela, ' class=beta', '') -- Corrigindo elementos não fechados corretamente SET @RetornoTabela = REPLACE(@RetornoTabela, '<th>', '</th><th>') SET @RetornoTabela = REPLACE(@RetornoTabela, '<tr></th>', '<tr>') SET @RetornoTabela = REPLACE(@RetornoTabela, '<th>Build<th ', '<th>Build</th><th ') SET @RetornoTabela = REPLACE(@RetornoTabela, '<th>Release Date</tr>', '<th>Release Date</th></tr>') SET @RetornoTabela = REPLACE(@RetornoTabela, '<td>', '</td><td>') SET @RetornoTabela = REPLACE(@RetornoTabela, '<tr></td>', '<tr>') SET @RetornoTabela = REPLACE(@RetornoTabela, '</tr>', '</td></tr>') SET @RetornoTabela = REPLACE(@RetornoTabela, '</th></td>', '</th>') SET @RetornoTabela = REPLACE(@RetornoTabela, '</td></td>', '</td>') -- Removendo elementos de entidades HTML SET @RetornoTabela = REPLACE(@RetornoTabela, ' ', ' ') SET @RetornoTabela = REPLACE(@RetornoTabela, '&kbln', '&kbln') SET @RetornoTabela = REPLACE(@RetornoTabela, '<br>', '<br/>') SET @dadosXML = CONVERT(XML, @RetornoTabela) DECLARE @Atualizacoes_SQL_Server TABLE ( [Ultimo_Build] VARCHAR(100), [Ultimo_Build_SQLSERVR.EXE] VARCHAR(100), [Versao_Arquivo] VARCHAR(100), [Q] VARCHAR(100), [KB] VARCHAR(100), [Descricao_KB] VARCHAR(500), [Lancamento_KB] VARCHAR(100), [Download_Ultimo_Build] VARCHAR(100) ) INSERT INTO @Atualizacoes_SQL_Server SELECT @dadosXML.value('(//table/tr/td[1])[1]','varchar(100)') AS Ultimo_Build, @dadosXML.value('(//table/tr/td[2])[1]','varchar(100)') AS [Ultimo_Build_SQLSERVR.EXE], @dadosXML.value('(//table/tr/td[3])[1]','varchar(100)') AS Versao_Arquivo, @dadosXML.value('(//table/tr/td[4])[1]','varchar(100)') AS [Q], @dadosXML.value('(//table/tr/td[5])[1]','varchar(100)') AS KB, @dadosXML.value('(//table/tr/td[6]/a)[1]','varchar(500)') AS Descricao_KB, @dadosXML.value('(//table/tr/td[7])[1]','varchar(100)') AS Lancamento_KB, @dadosXML.value('(//table/tr/td[6]/a/@href)[1]','varchar(100)') AS Download_Ultimo_Build DECLARE @Url_Ultima_Versao_SQL VARCHAR(500) = (SELECT TOP(1) Download_Ultimo_Build FROM @Atualizacoes_SQL_Server), @Ultimo_Build VARCHAR(100) = (SELECT TOP(1) Ultimo_Build FROM @Atualizacoes_SQL_Server), @Descricao_KB VARCHAR(500) = (SELECT TOP(1) Descricao_KB FROM @Atualizacoes_SQL_Server) IF (@Versao IS NOT NULL) BEGIN SELECT * FROM @Atualizacoes_SQL_Server END ELSE BEGIN IF (CONVERT(VARCHAR(100), SERVERPROPERTY('ProductVersion')) >= @Ultimo_Build) SELECT 'SQL Server ATUALIZADO!' AS SQL_Server_Atualizado ELSE BEGIN SELECT 'SQL Server DESATUALIZADO!!' AS SQL_Server_Desatualizado UNION ALL SELECT '----------------------------------------' UNION ALL SELECT 'Versão mais atual: ' + @Ultimo_Build + ' (' + @Descricao_KB + ')' UNION ALL SELECT 'Download da versão mais atual: ' UNION ALL SELECT @Url_Ultima_Versao_SQL UNION ALL SELECT '----------------------------------------' UNION ALL SELECT 'Sua versão: ' + CONVERT(VARCHAR(100), SERVERPROPERTY('ProductVersion')) + ' (' + CONVERT(VARCHAR(100), SERVERPROPERTY('ProductLevel')) + ')' SELECT * FROM @Atualizacoes_SQL_Server END END END |
Examples of use
Now I will demonstrate some examples of how this Stored Procedure works in practice.
Retrieving the last update from an updated SQL Server 2017
|
1 2 3 4 |
SELECT @@VERSION -- Retorna a última versão do SQL Server instalado na instância atual EXEC dbo.stpRetorna_Ultima_Versao_SQL |
Result:
Retrieving the last update from a SQL Server 2017
|
1 2 3 4 |
SELECT @@VERSION -- Retorna a última versão do SQL Server instalado na instância atual EXEC dbo.stpRetorna_Ultima_Versao_SQL |
Result:
Retrieving the last update from a SQL Server 2008 R2
|
1 2 3 4 |
SELECT @@VERSION -- Retorna a última versão do SQL Server instalado na instância atual EXEC dbo.stpRetorna_Ultima_Versao_SQL |
Result:
Retrieving the last update from a SQL Server 2019
|
1 2 3 4 |
SELECT @@VERSION -- Retorna a última versão do SQL Server instalado na instância atual EXEC dbo.stpRetorna_Ultima_Versao_SQL |
Result:
I need to show ALL updates of a version of SQL Server
If you want to show all updates of the desired version of SQL Server and not just the last one, you can use the code below (after the 150 line):
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
SELECT Versoes.linha.value('(td[1])[1]','varchar(100)') AS Ultimo_Build, Versoes.linha.value('(td[2])[1]','varchar(100)') AS [Ultimo_Build_SQLSERVR.EXE], Versoes.linha.value('(td[3])[1]','varchar(100)') AS Versao_Arquivo, Versoes.linha.value('(td[4])[1]','varchar(100)') AS [Q], Versoes.linha.value('(td[5])[1]','varchar(100)') AS KB, (CASE WHEN Versoes.linha.value('(td[6]/a)[1]','varchar(500)') IS NOT NULL THEN Versoes.linha.value('(td[6]/a)[1]','varchar(500)') ELSE Versoes.linha.value('(td[6])[1]','varchar(500)') END) AS Descricao_KB, Versoes.linha.value('(td[7])[1]','varchar(100)') AS Lancamento_KB, Versoes.linha.value('(td[6]/a/@href)[1]','varchar(100)') AS Download_Ultimo_Build FROM @dadosXML.nodes('//table/tr') Versoes(linha) WHERE Versoes.linha.value('(td[1])[1]','varchar(100)') IS NOT NULL |
Result:
Conclusion
Well guys, I hope you enjoyed this article. I am starting to use it in environments to do a quick check up and it turned out to be very practical. If you want to create monitoring for this in your environment by analyzing all instances, you can create this Stored Procedure on all instances and use the returned data to send automated emails as needed.
In this monitoring scenario, another very interesting solution is that of Reginaldo Silvawho made the post Get-SQLBuildVersion a Different Way to Stay Updated, where he shared a solution written in PowerShell, which he developed, where he passes the instance name as a parameter and the script already does this validation and parameters to email the result. Very practical to use in multiple instances.
Like Reginaldo, I will highlight the article Cumulative update, apply or not, that's the question! do Luciano Moreira (Luti), where he sets out his views on the constant application of SQL Server updates (I agree with him), since many DBA's are not in the habit of keeping SQL Server updated for fear of introducing a new bug after applying an update.
In my view, this doesn't make much sense these days, as these events are quite rare, and as you always apply updates, your environment is always up to date with new features, bug fixes, and security enhancements, as well as having a process more mature and common application updates in the company.
Ah, remember that from SQL Server 2017, we will have no more Service Packs, only Cumulative Updates, as documented in this article here.
That's it, guys.
I hope you enjoyed this article and even more!
Regarding SQL Server software upgrade, it should be noted that we currently have two active maintenance models: the Incremental Servicing Model (ISM) and the Modern Servicing Model (MSM), where MSM applies only to the 2017 (and later) version of SQL Server whereas ISM applies for versions up to and including 2016.
Note that in the ISM model new service packs continue to be released (besides the cumulative updates, of course).
The launch of MSM was explained in the post “Announcing the Modern Servicing Model for SQL Server”, published on the official blog “SQL Server Release Updates”, which served as a starting point for the publication of the article “SQL Server software update” [ https://social.technet.microsoft.com/wiki/pt-br/contents/articles/51093.atualizacao-de-software-sql-server.aspx ], where the characteristics of the ISM and MSM models are described as well as glossary with the acronym soup (COD, CU, RTM, GDR etc).
As a curiosity, in the Microsoft TechNet Gallery there is the script “Determining the version and edition of SQL Server and its components” [ https://gallery.technet.microsoft.com/Determining-the-version-dbc1c53a ] which uses a different technique to determine which update to apply on T-SQL and PS versions.
José Diz (SQL Port)
Thanks for the comments, Jose! Big hug