Is there a way to force sp_send_dbmail to use ANSI, or to not include the Unicode prefix?

Question

This is pretty trivial, but if someone has the answer handy, I'd appreciate it.

I use the msdb..sp_send_dbmail sproc as a lightweight way to distribute simple reports. This often entails producing a CSV. However, the sproc includes the Unicode little-endian prefix FF FE. This confuses Excel, causing it to not automatically parse the CSV into columns.

There are a number of workarounds: use a real reporting platform (we have Crystal, but it's never worked reliably); send the file to a little app that converts the file to ANSI and sends it on; use tab delimiters (my favorite); or educate the recipients about how to work with CSVs (just kidding, we all know that's not possible).

However, I'd love to just flip a switch somewhere and tell MSSQL to stop creating the attachments as Unicode, as I have no such need. I haven't found such a setting yet; is this possible?

Thanks!

---

EDIT

Based on Rohan's answer, this script in a SQL Agent job step almost works:

$reportQuery = "EXEC Routine..TopTenReviewsReport @DoNotLog = 1"
$filePath = "\\SQLBI001\RawData\TopTenReviewsReport.csv"
$mailQuery = "
  EXEC msdb..sp_send_dbmail
  @profile_name = 'X',
  @recipients = 'X',
  @subject = 'X',
  @file_attachments = '" + $filePath + "'"

Invoke-Sqlcmd -query $reportQuery | export-csv $filePath
$result = Invoke-Sqlcmd -query $mailQuery

The file now has a "#TYPE System.Data.DataRow" prefix, instead of 0xFF FE, but it's now ANSI, so that's something.

Answer 1

I think you can generate the reports using powershell and then use DBMail to mail the attachments

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' "

Source: http://blogs.msdn.com/b/sqlagent/archive/2011/07/06/email-query-output-as-ansi-unicode-file-attachment-using-powershell-and-database-mail.aspx