Streamline TSQL Result Delivery: Automate Emailing of TSQL Results in HTML Format using PowerShell - Any-TSQL Results to HTML EMail

 

In this blog post, we will explore a useful PowerShell script that allows you to execute TSQL queries and send the results via email. The script is versatile and can work with any type of TSQL query. It also provides options to customize the font and background color of the query results. Whether you want to schedule it using Windows Task Scheduler or SQL Server Agent, this script will help you streamline your reporting process.

The PowerShell Script:

Below is the PowerShell script that facilitates sending TSQL query results via email:

Parameters (Line 9-23): These are the input variables that can be modified according to your requirements. Replace the placeholder values with your specific details.

Please find the Github link

<#
Created by Lince Sebastian
For more please visit 
https://www.dbascrolls.com/

#>

# Database connection settings
$serverName = "localhost"
$databaseName = "DatabaseName"
# T-SQL query to execute
$query = "exec newtsql"

# Define color variables
$headerFontColor = "white"
$headerBackgroundColor = "green"
$cellFontColor = "black"
$cellBackgroundColor = "lightyellow"

# SQL Server Database Mail settings
$subject = "T-SQL Results"
$profileName = "MailProfile" # Replace with the name of your Database Mail profile
$recipients = "recipient@outlook.com"    # Replace with the email address of the recipient

$connectionString = "Server=$serverName;Database=$databaseName;Integrated Security=True;"

# Function to execute T-SQL query and convert results to HTML table
function Get-TSQLResultsAsHtmlTable {
    param (
        [string]$connectionString,
        [string]$query,
        [string]$headerFontColor = "black",
        [string]$headerBackgroundColor = "lightgray",
        [string]$cellFontColor = "black",
        [string]$cellBackgroundColor = "white"
    )
    $connection = New-Object System.Data.SqlClient.SqlConnection
    $connection.ConnectionString = $connectionString
    $command = $connection.CreateCommand()
    $command.CommandText = $query

    $dataAdapter = New-Object System.Data.SqlClient.SqlDataAdapter $command
    $dataTable = New-Object System.Data.DataTable
    $dataAdapter.Fill($dataTable) | Out-Null

    $columns = $dataTable.Columns
    $htmlTable = ""
    foreach ($column in $columns) {
        $columnName = $column.ColumnName
        # Extract just the column name without the table name prefix
        if ($columnName.Contains(".")) {
            $columnName = $columnName.Split(".")[-1]
        }
        $htmlTable += ""
    }
    $htmlTable += ""
    foreach ($row in $dataTable.Rows) {
        $htmlTable += ""
        foreach ($column in $columns) {
            $cellValue = $row[$column]
            $htmlTable += ""
        }
        $htmlTable += ""
    }
    $htmlTable += "
$columnName
$cellValue
"

    $connection.Close()

    return $htmlTable
}


# Get the HTML table with custom font and background colors
$htmlTable = Get-TSQLResultsAsHtmlTable -connectionString $connectionString -query $query `
    -headerFontColor $headerFontColor -headerBackgroundColor $headerBackgroundColor `
    -cellFontColor $cellFontColor -cellBackgroundColor $cellBackgroundColor


# Build the email body with HTML table
$emailBody = @"





Hello, 
 


 
Please find the results in the table below:

$htmlTable


"@

# Replace single quotes with double quotes in the email body
$emailBody = $emailBody -replace "'", "''"

# Send the email using sp_send_dbmail
$sendMailQuery = @"
EXEC msdb.dbo.sp_send_dbmail
    @profile_name = '$profileName',
    @recipients = '$recipients',
    @subject = '$subject',
    @body = '$emailBody',
    @body_format = 'HTML';
"@

# Execute the query using the SQL Server connection
$connection = New-Object System.Data.SqlClient.SqlConnection
$connection.ConnectionString = $connectionString
$command = $connection.CreateCommand()
$command.CommandText = $sendMailQuery

$connection.Open()
$command.ExecuteNonQuery()
$connection.Close()