troubleshooting Question

Simple example of sending SQL query result as HTML formatted table in an email using Powershell

Avatar of David Megnin
David MegninFlag for United States of America asked on
PowershellScripting Languages
10 Comments2 Solutions3367 ViewsLast Modified:
I tried piecing together a couple scripts, one that send a single value to an email address and another that writes a query result to a text file, but I don't know Powershell well enough to get it to work.

I want to send the result of an SQL query of just a few columns as an HTML formatted table in an email message using Send-MailMessage.

Here is what I've got.  It prints the table to the console and the output file, but the email fails with an error.

<# connection string and SQL command to execute #>
[String] $now = Get-Date
[String] $to = 'dmegnin@careersourcebroward.com'
[String] $connectionString = 'Server=MSQLServ; Database=ITASync; Integrated Security=SSPI;'
[String] $query = @'
  SELECT Provider, LoginDate, DATEDIFF(day, LoginDate, GetDate()) as Diff
    FROM Logins
    WHERE DATEDIFF(day,LoginDate, GetDate()) IN (37)
'@


$connection = New-Object System.Data.SqlClient.SqlConnection
$connection.ConnectionString = $connectionString
$connection.Open()
$command = $connection.CreateCommand()
$command.CommandText = $query

$result = $command.ExecuteReader()

$table = new-object “System.Data.DataTable”
$table.Load($result)

$format = @{Expression={$_.Provider};Label="Provider Name";width=30},@{Expression={$_.LoginDate};Label="Login Date"; width=25},@{Expression={$_.Diff};Label="Days"; width=8}

$table | Where-Object {$_.Provider -like "*PROFESSOR" -and $_.Diff -lt 100} | format-table $format

$table | Where-Object {$_.Provider -like "*PROFESSOR" -and $_.Diff -lt 100} | format-table $format | Out-File C:\Temp\ee\Providers.txt

Send-MailMessage -BodyAsHtml -Body "$table | Where-Object {$_.Provider -like "*PROFESSOR" -and $_.Diff -lt 100} | format-table $format" -SmtpServer mail.careersourcebroward.com -From "Contract Expiration Database <csbd@careersourcebroward.com>" -To $to -Subject "Provider: $($_.Provider) logged in $($_.Diff) days ago."

$connection.Close()

If there's a cleaner or better with to do this, I'd like to learn it.  Thank you!
ASKER CERTIFIED SOLUTION
Join our community to see this answer!
Unlock 2 Answers and 10 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 2 Answers and 10 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros