Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
    
Clarification: I'm testing with Excel 2007, but 2K10 seems to have the same issue. Removing the prefix is enough for Excel to parse the file, it does not have to be ANSI. –  Jon of All Trades Sep 28 '11 at 21:51
    
@Surfer513: Thanks for the tag, I could have sworn I tried that and it did not exist. –  Jon of All Trades Sep 29 '11 at 14:28
add comment

1 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

share|improve this answer
    
Hmm, and PowerShell is available via SQL Agent, which (I should have mentioned) is how I'm starting the jobs. Write-host is not available in that context, but that's minor. Unfortunately, the generated file has a different prefix: "#TYPE System.Data.DataRow". It is indeed ANSI now, though. –  Jon of All Trades Sep 29 '11 at 15:02
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.