How to Email the results of a SQL Query in CSV format

It is possible to use Powershell and JAMS to perform a SQL query and email the results as a CSV to an email recipient. This is done using the SQLServer Powershell module.

More information about the Module can be found here:
http://technet.microsoft.com/en-us/library/cc281720.aspx

With Powershell 5.x and higher, it is possible to install using the Install-Module command using:

Install-Module SQLServer

Once that is complete, it is then possible to use the script below in the source of a JAMS job that uses the Powershell execution method.

Import-Module SQLServer

$query = @"
SELECT * from Table
"@
$results = Invoke-SqlCommand -Sql $query | format-Table -AutoSize | Out-String -Width 4096
Invoke-SqlCommand -Sql $query | export-Csv c:\tempResults.csv -noTypeInformation

$attachment = "c:\temp\Results.csv"
Send-MailMessage -To JAMS@MVPSI.com -Subject "Licenses that are close to expiring" -From 
DanielS@MVPSI.com -body $results -Attachments $attachment

The script shown above will generate a CSV from the query result, and then email it to the recipient.

Comments