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

07 August,2015 by Jack Vamvas

 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 ?

 

 

 


Author: Jack Vamvas (http://www.sqlserver-dba.com)


Share:

Verify your Comment

Previewing your Comment

This is only a preview. Your comment has not yet been posted.

Working...
Your comment could not be posted. Error type:
Your comment has been posted. Post another comment

The letters and numbers you entered did not match the image. Please try again.

As a final step before posting your comment, enter the letters and numbers you see in the image below. This prevents automated programs from posting comments.

Having trouble reading this image? View an alternate.

Working...

Post a comment on Powershell Add-Content – How to add another query result to an existing html report


sqlserver-dba.com | SQL Server Performance Tuning | SQL Server DBA:Everything | FAQ | Contact|Copyright & Disclaimer