Microsoft SQL Server on Windows Azure Virtual Machines

The writers of this blog are a part of the Microsoft SQL Server team. We will be blogging about our tools, scripts, webcasts, and miscellaneous tips that will help you get the most out of SQL Server components on Windows Azure Virtual machines

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

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

  • Comments 1
#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

 

Leave a Comment
  • Please add 7 and 1 and type the answer here:
  • Post
  • 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.

Page 1 of 1 (1 items)