An auto-updating SQL Server Release History spanning the entire product line.
Exposed to Microsoft’s new Data Explorer I was impressed by the power of Microsoft’s new tool! Using a WSIWIG data manipulation tool one can create filtered, shaped, pivoted, purged, and preened data sets within Excel – and have them auto-update when data connections are refreshed.
During client site audits I invariably find need for a list of SQL Server versions including interim patches and cumulative updates to accurately determine how current a platform is. I find myself creating a spreadsheet from web-references which is painstaking and quickly obsolete.
Microsoft’s Data Explorer can consume data from almost any common data source, however it also consumes web pages and HTML tables as a data source – which gave me an idea…
I frequent http://sqlserverbuilds.blogspot.com as they maintain a detailed list of SQL Server releases broken up by version, and frequently updated. I grew tired of manually reviewing each table for updates whenever I needed the latest list. I wanted something automatic.
Data Explorer proved very versatile in this task – I pointed DE it to the URL, created a query per table (which translates to one query per SQL Server version), and the persistent queries refresh when Excel data is refreshed.
The M code required to download/capture the HTML table is trivial:
| 1 | <span class="lnum"> 1: </span>let |
| 1 | <span class="lnum"> 2: </span> Src = Web.Page(Web.Contents(<span class="str">"http://sqlserverbuilds.blogspot.com/"</span>)){1}[Data] |
| 1 | <span class="lnum"> 3: </span><span class="kwrd">in</span> |
| 1 | <span class="lnum"> 4: </span> Src |
| 1 |
It was just as simple to join the version-dependent tables into a master list spanning the complete release history of SQL Server since inception, also using a persistent query, also refreshed. Joining the HTML tables together is a one liner.
| 1 | <span class="lnum"> 1: </span>let |
| 1 | <span class="lnum"> 2: </span> Source = Table.Combine({#<span class="str">"2012"</span>,#<span class="str">"2008 R2"</span>,#<span class="str">"2008"</span>,#<span class="str">"2005"</span>,#<span class="str">"2000"</span>,#<span class="str">"7"</span>}), |
| 1 | <span class="lnum"> 3: </span> Hidden= Table.RemoveColumns(Source,{<span class="str">"File Version"</span>}) |
| 1 | <span class="lnum"> 4: </span><span class="kwrd">in</span> |
| 1 | <span class="lnum"> 5: </span> Hidden |
In a matter of minutes I created a solution which automatically updates itself as the web site updates. Below is an embedded copy of the spreadsheet courtesy of SkyDrive.
Download Microsoft Excel 2013’s Data Explorer.