1

I have a procedure that generates a resultset, which I attach as a CSV file attachment in sp_sendmail. However, the resulting file is encoded UCS-2 LE BOM (according to notepad++). This causes the resulting file to not "parse" into columns but opens with all values in column A. If I change the encoding to UTF-8 it opens fine.

Problem:

This is an easy fix for me... but the recipients would have no idea how to do this. Can I change the file to be produced as UTF-8?

Code used to produce send the mail:

exec msdb.dbo.sp_send_dbmail
            @subject                     = @subject
        ,   @body                        = @message
        ,   @body_format                 = 'html'
        ,   @recipients                  = @recipients_to
        ,   @copy_recipients             = @recipients_cc
        ,   @importance                  = 'High'
        ,   @sensitivity                 = 'Confidential'
        ,   @query                       = N'select * from ##Report'
        ,   @execute_query_database      = @dbName
        ,   @attach_query_result_as_file = 1
        ,   @query_attachment_filename   = @file_name
        ,   @query_result_header         = 1
        ,   @query_result_no_padding     = 1
        ,   @query_result_separator      = ','
        ,   @query_result_width          = 32767;
1

I would suggest you to generate the attachment with powershell and then send the attachment:

$reportQuery = "select * from ##Report"
$filePath = "\\aaaa\bbbb\attach.csv"
$mailQuery = "
  EXEC msdb..sp_send_dbmail
  @profile_name = '..',
  @recipients = '..',
  @subject = '..',
  @file_attachments = '" + $filePath + "'",
    ..... (others parameters)

Invoke-Sqlcmd -query $reportQuery | export-csv $filePath -encoding "unicode"
$result = Invoke-Sqlcmd -query $mailQuery
1
  • This could be a partial solution. I can always export the data via PoSh (or even BCP - but would need to create/include the header row) and then attach the resulting file. yeah... this could work! Thanks – SqlNightOwl Feb 3 at 15:06
0

This seems to be some sort of limitation in how the procedure sp_send_dbmail works but there's a workaround you try by implementing a few changes to the procedure via this StackOverflow answer.

Warning: I don't recommend altering the actual procedure itself, rather I'd make your own copy of the procedure to work from, to minimizing hosing your instance.

Also, only tangentially related but I found this RedGate article on sending mail via CLR instead interesting, and perhaps a helpful thing for you to look into as an alternative.

2
  • I'm always happy to poke around in MS provided procedures since I almost always learn something. In this case, any CU that updates DB Mail could override any fix I make and then I'm back wondering why the attachment is "bad". I'll look at the RG article, although or production DBA's are old school and have repeatedly not only said NO but ... well you know the rest. – SqlNightOwl Feb 3 at 14:57
  • @SqlNightOwl Right, which is another good reason you shouldn't modify the system procedure itself, rather make your own copy of the procedure (bonus points for putting it in a user database) and leverage that copy. :) – J.D. Feb 3 at 15:12

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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