Powershell way to send email with query result in SQL server

Question

Currenly, I am working to setup a powershell job in SQL server to send database mail for some results in a table format. Here is my script:

$SMTPProperties = @{
    To = "[email protected]","[email protected]"
    Cc = "[email protected]"
    From = "[email protected]"
    Subject = "SQL Report Status"
    SMTPServer = "192.168.xx.xx"
}

$server   = "192.168.xx.xx"
$database = "DBName"
$username = "abc"
$password = "abc124"
$query = "select top 10* from testing"


function ExecuteSqlQuery ($Server, $Database, $query) {


    $Connection = New-Object System.Data.SQLClient.SQLConnection
    $Connection.ConnectionString = "server='$Server';database='$Database';User ID='$username'; Password='$password';trusted_connection=true;"
    $Connection.Open()
    $Command = New-Object System.Data.SQLClient.SQLCommand
    $Command.Connection = $Connection
    $Command.CommandText = $query
    $Reader = $Command.ExecuteReader()

    $Datatable = New-Object System.Data.DataTable
    $Datatable.Load($Reader)
    $Connection.Close()

    return $Datatable
}

$resultsDataTable = New-Object System.Data.DataTable
$resultsDataTable = ExecuteSqlQuery $Server $Database $query


Send-MailMessage @SMTPProperties -Body $query -BodyAsHTML | Format-Table

A few questions comes:

1. In @SMTPProperties, how can I send to multiple recipients? Solved
2. The script works but in content of the received email, it simply returns text of the query (select top 10* from testing). It is not the query result.
3. Is my script correct to output a HTML table in the email content? If not , how can I change it?
4. How can I run above without provide UID and Password in above script.

Thank you.

Answer 1

For more recipients you can use Cc field

I use this for sending mails for my Powershell scripts.

Send-MailMessage `
-Credential $anonCredentials `
-From [email protected] `
-To [email protected] `
-Cc "[email protected]","[email protected]","[email protected]" `
-Subject "Enter your subject" -Body "This is an automated message from the server with some data" `
-SmtpServer 192.168.x.x `
-Attachments "C:\ThedataIwanttosend.rar"

If you try to export your report in an html file or something else and then mail it to those that must receive it? does this solution works for you? if you run your script you have any results?

You are setting $Query to a text and then you never update with something new.

your -body takes $query as text so it is right to get that text as a mail.

Send-MailMessage @SMTPProperties -Body $query -BodyAsHTML | Format-Table

Are you getting the right data from the Function you are using? if yes then you have to put those results in a variable and write that variable as a body.

now you have :

$query = "select top 10* from testing"
Send-MailMessage @SMTPProperties -Body $query -BodyAsHTML | Format-Table

so the mail you are getting gets the -body data from the $query variable that is the text you set on the $query variable.

If you want something else in that mail body you have to save it into the $query variable or create a new variable with the results and then add it to the -Body.

Hope it helps.