07 August,2015 by Tom Collins
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
Expand your Powershell mind – Three key cmdlets - SQL Server DBA
I want to become a DBA. What should I first learn ?
This is only a preview. Your comment has not yet been posted.
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.
Posted by: |