How to take SQL results to make an HTML document using PowerShell

Introduction

This sample provides a script for IT pro or Windows customers to quickly make SQL results to an HTML document using PowerShell.

Scenarios

For SQL records that have HTML tags in them, customers or IT pro might want to take records and create an HTML document with PowerShell that follows the format of tags. This sample provides a solution to take SQL results and make an HTML document.

Script

• Use intTestDb.sql to initialize test database and table.

• Open the script in archive.

• Edit line 37 and change parameters yourself.

PowerShell
编辑脚本|Remove
SqlResultsToHtml -ServerInstanceName "." -DataBaseName "TestDb" -Sql "select * from TestTable" 

• Run the script and you will see similar output like the screenshot at below.

 

Here are some code snippets for your reference.

 

PowerShell
编辑脚本|Remove
    #Query SQL 
    $SqlResults = Invoke-Sqlcmd -ServerInstance $ServerInstanceName -Database $DataBaseName -Query $Sql  
    #SQL to Html string 
    $Html = $SqlResults | Select-Object * -ExcludeProperty RowError,RowState,Table,ItemArray,HasErrors | ConvertTo-Html | Out-String  
    #Html Decode 
    [System.Reflection.Assembly]::LoadWithPartialName("System.web"| Out-Null 
    $Html = [System.Web.Httputility]::HtmlDecode($Html) 
    #Save the Html Web Page 
    ConvertTo-HTML  -PostContent $Html | Out-File $OutputHtmlFilePath 
    #Open Html 
    Invoke-Item $OutputHtmlFilePath

Prerequisites

• PowerShell 2.0 or above versions

Microsoft All-In-One Script Framework is an automation script sample library for IT Professionals. The key value that All-In-One Script Framework is trying to deliver is Scenario-Focused Script Samples driven by IT Pros' real-world pains and needs. The team is monitoring all TechNet forums, IT Pros' support calls to Microsoft, and script requests submitted to TechNet Script Repository. We collect frequently asked IT scenarios, and create script samples to automate the tasks and save some time for IT Pros. The team of All-In-One Script Framework sincerely hope that these customer-driven automation script samples can help our IT community in this script-centric move.