Email Query output as ANSI / UNICODE file attachment using Powershell and Database Mail


#launch sqlps.exe to run the followign script
#update servername,email profile, recipient list that matches your environment
 
$instanceName = "MACHINE\SQLINSTANCE"
$targetFileAnsi = "c:\temp\result_ansi.csv"
$targetFileUnicode = "c:\temp\result_unicode.csv"
$dbQuery = "select SERVERPROPERTY('ServerName') As ServerName, Name, crdate from sysdatabases"
 
Write-host "Running SQL Query to export to ANSI CSV file"
Invoke-Sqlcmd -Query $dbQuery -ServerInstance $instanceName | export-csv $targetFileAnsi
 
Write-host "Running SQL Query to export to Unicode CSV file"
Invoke-Sqlcmd -Query $dbQuery -ServerInstance $instanceName | export-csv $targetFileUnicode -encoding "unicode"
 
 
$dbMailQuery1 = "execute msdb..sp_send_dbmail
 @profile_name = 'msft_profile',
 @recipients = 'seths@microsoft.com',
 @subject = 'Email with Attachment - ANSI attachment',
 @body_format = 'TEXT',
 @body = 'This email has ANSI file attached to it',
 @file_attachments = 'c:\temp\result_ansi.csv' "
 
Write-host "Sending email (using dbmail), include ANSI file as attachment"
Invoke-Sqlcmd -Query $dbMailQuery1 -ServerInstance $instanceName
 
 
$dbMailQuery2 = "execute msdb..sp_send_dbmail
 @profile_name = 'msft_profile',
 @recipients = 'seths@microsoft.com',
 @subject = 'Email with Attachment - UNICODE attachment',
 @body_format = 'TEXT',
 @body = 'This email has UNICODE file attached to it',
 @file_attachments = 'c:\temp\result_unicode.csv' "
Write-host "Sending email (using dbmail), include UNICODE file as attachment"
Invoke-Sqlcmd -Query $dbMailQuery2 -ServerInstance $instanceName

 


#This posting is provided "AS IS" with no warranties, and confers no rights. 
#Use of included script samples are subject to the terms specified athttp://www.microsoft.com/info/cpyright.htm

 

Comments (1)

  1. Sethu,

    I can't use the SQL Powershell solution, as it needs to run in either SQL Server 2005 or 2008. I need a solution to allow a SQL Agent Job step to create the ANSI formatted flat text file from the query output of my complex SQL Stored Procedure, which I can then send in a separate SQL Agent job step using sp_send_dbmail.