Powershell Add-Content – How to add another query result to an existing html report

 Question: Could you provide a method on how can I add another sql query result to and existing powershell  html report file.

For example, I want to use the code on SQL Database Size report using Powershell , but need to supply an additional resultset to the Powershell report.

Answer: You can add another sql query resultset to the existing powershell html report . You will need to :

a)      Create an additional query request and recordset  and add to a new System.Data.Datatable

b)      Use Powershell Add-Content to add the sql resultset to an existing powershell html  report

For example using the script on SQL Database Size report using Powershell

Step 1- Create the extra  sql recordset . This example connects to a target sql server instance and returns a list of databases. An example:

 $dt_report_database_list = new-object "System.Data.DataTable"
 $cn_db_list= new-object System.Data.SqlClient.SqlConnection "server=$targetServer;database=$databaseName;Integrated Security=sspi"
   $cn_db_list.Open()
    $sql = $cn_db_list.CreateCommand()
    $sql.CommandText = "select name from sys.databases"
    $rdr = $sql.ExecuteReader()
    $dt_report_database_list.Load($rdr)
    $cn_report_database_list.Close()

Step 2 – Use Add-Content to add the System.Data.DataTable to add to the existing report. Place the following code UNDER the existing code which uses Set_Content and generates the html report. The reason it’s placed under is because you are using Add-Content. You’ll notice there is already a Set-Content reference. The use of Set-Content is to write data to a text file. Set-Content replaces the existing contents of Test.txt with the new text specified in the Set-Content command.To append text to a file, use the Add-Content cmdlet.

$dt_report_database_list | select * -ExcludeProperty RowError, RowState, HasErrors, Name, Table, ItemArray | ConvertTo-Html -head $reportstyle -body "

SQL Server database list

" | Add-Content $outputfilefull  

 Read More on Powershell Scripts ,

Expand your Powershell mind – Three key cmdlets - SQL Server DBA

Powershell Scripts for DBA

I want to become a DBA. What should I first learn ?