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?
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.