Strange characters at the beginning of query results when attaching HTML file to email using msdb.dbo.sp_send_dbmail

Question

I’m trying to send an email with HTML file attachment using msdb.dbo.sp_send_dbmail. The sp sends the email but the attachment has a couple of strange characters added to the start of the file and causing it not to open properly when opened from the email client such as Gmail. I just fount out the first two letters are ÿþ. When you try to click the attachment, Gmail says no preview available. If you open the email and double click on the attachment, it opens in text format with the HTML code. The interesting thing is that if I save it to my hard drive then double click on it, it opens fine.

Here is my code:


        Declare @html nvarchar(max)
        Declare @query nvarchar(max)

        set @html = '
        <!DOCTYPE html>
            <html>   
                <head>   
                    <style> table  td {border: 1px solid #dddddd;text-align: left;padding: 8px;}
                        th {border: 1px solid #dddddd;text-align: center;padding: 8px;}   
                        .main {bgcolor:#3399ff;background:#3399ff;background-color:#3399ff;}   
                        .numbrs {text-align: right}   
                    </style>   
                </head>   
                <body>   
                <table>     
                    <tr><td class="main">Invoice #</td><td>123456</td>
                    <td class="main">Invoice Date</td><td>12/31/2022</td>
                </table>
            </html>
                '
        Set @query= 'set nocount on; SELECT ''' + @html + ''' AS Results'
        EXEC msdb.dbo.sp_send_dbmail
        @profile_name = 'TEST'
        ,@recipients = 'name@domain.com'
        ,@subject = 'Test Email with Attachment'
        ,@body =  'Please see attached file'
        ,@body_format ='HTML'
        ,@Query_no_truncate = 1
        ,@Execute_Query_Database='TEST'
        ,@query =@query
        ,@query_attachment_filename = 'Report.html'
        ,@attach_query_result_as_file = 1

When I double click on the email attachment to open it, it opens as a text in my browser like this:

here is how it should look like if you copy and paste the HTML code in notepad and saved it as a .html file then click on it to open it in a browser:

<!DOCTYPE html>
<html>

<head>
  <style>
    table td {
      border: 1px solid #dddddd;
      text-align: left;
      padding: 8px;
    }
    
    th {
      border: 1px solid #dddddd;
      text-align: center;
      padding: 8px;
    }
    
    .main {
      bgcolor: #3399ff;
      background: #3399ff;
      background-color: #3399ff;
    }
    
    .numbrs {
      text-align: right
    }
  </style>
</head>

<body>
  <table>
    <tr>
      <td class="main">Invoice #</td>
      <td>123456</td>
      <td class="main">Invoice Date</td>
      <td>12/31/2022</td>
  </table>

</html>

Here is how the attachment shows in my browser:

�� <!DOCTYPE html> <html> <head> <style> table td {border: 1px solid #dddddd;text-align: left;padding: 8px;} th {border: 1px solid #dddddd;text-align: center;padding: 8px;} .main {bgcolor:#3399ff;background:#3399ff;background-color:#3399ff;} .numbrs {text-align: right} </style> </head> <body> <table> <tr><td class="main">Invoice #</td><td>123456</td> <td class="main">Invoice Date</td><td>12/31/2022</td> </table> </html>

EDIT: I just fount out the first two letters are ÿþ which is 0xfffe in UTF-8 according to this SO question.

That's a really strange way of sending an attachment. I'm guessing the empty column header is what is appearing there. Can you paste in the beginning of the HTML attachment *exactly* using Copy/Paste (without retyping anything)? Is there any reason you are doing this as an attachment anyway? Why not send a normal HTML email directly in the `@body`? And if you are just putting in text as the actual body then `@body_format ='HTML'` is wrong. — Charlieface, Aug 14 '22 at 03:20
Thanks @Charlieface. This is the HTML code exactly using copy/paste. The HTML code and the rest of the SP is far more complected than this but I summarized it for clarity. Still the above code will produce the same result and the same issue I'm having (you can test it if you like). I can't send it in the body because the customer requires it to be an attachment for easy printing. — BlueTube, Aug 14 '22 at 04:04
No please show the HTML *attachment* that you receive, that contains the extra characters. A screenshot is useless. We need to see the *actual* characters you received. — Charlieface, Aug 14 '22 at 04:11
@Charlieface Sorry, misunderstood your comment. Just edited the post and added it now. Thanks for you help with this. — BlueTube, Aug 14 '22 at 04:17
Can you paste in the file as you see it in Notepad rather than your browser, as there appears to be an encoding issue? `DOCTYPE` declaration should have no preceding spaces, so try `set @html = ' ` — Charlieface, Aug 14 '22 at 04:21
@Charlieface thank you! I tried to change the code to remove any spaces before the ' ' tag but still the same issue. If I save the attachment to my hard drive then open it in Notepad, there is no space or anything before the tag — BlueTube, Aug 14 '22 at 04:37
Seems like there's something weird and wonderful going on with your SQL Server. Those two characters, at least the ones you've pasted into SO, are two copies of the U+FEFF "Zero Width No-Break Space" character, aka. Byte Order Mark, aka. Preamble, followed by a space. If you save the file attachment from the mail message to disk and open it in a hex editor, even VS Code with the Hex Editor extension installed, what bytes actually appear before the ` ` tag for you? — AlwaysLearning, Aug 14 '22 at 05:44
`sp_send_dbmail` is extremely limited and, really, it should not ever be used for anything other than sending alert emails to SQL Server administrators. If I were to guess I'd say that the query is outputting UCS-2 encoded results that get attached to the mail message, but the `Content-Type` header on that message part contains something other than `Content-Type: text/html; charset=ucs-2`, probably `Content-Type: text/plain; charset=ucs-2`, and that's why the mail client wants to open it as a text file. — AlwaysLearning, Aug 14 '22 at 05:53
@AlwaysLearning I see two of U+FFFD "Replacement Character" it looks like it got messed up by the browser though, so you are probably right that it was originally U+FEFF — Charlieface, Aug 14 '22 at 09:57
Try `@query_result_header = 0`. Also try saving the file to the local disk of the SQL Server machine, then use `@file_attachments = 'yourFile.html'` — Charlieface, Aug 14 '22 at 10:00
Yes that's a BOM U+FFFE, but it's for UTF-16, and it looks like the browser is expecting UTF-8. You may want to look at this answer https://stackoverflow.com/a/14670451/14868997 — Charlieface, Aug 14 '22 at 10:47
Thanks @Charlieface. I saw this before but unfortunately this is not an option for me. The customer does not want us to mess with the msdb. Is there is anyway we can make it work any other way? — BlueTube, Aug 14 '22 at 19:32
Not really, you should use a proper mass mail client or service. `sp_send_dbmail` is really only designed for alerts to a DBA, not as a generalized mail client, as noted by @AlwaysLearning — Charlieface, Aug 14 '22 at 19:35
@Charlieface and thanks to both of you. Are there any other ways to attach a PDF or Html file to an email (i.e. saving the file in a column) without accessing the local drives of SQL server or making changes to the built-in SPs? — BlueTube, Aug 15 '22 at 01:34
Why use T-SQL at all? Why not use a proper scripting language (hint: T-SQL is not one) such as Python, C# or Powershell? `SmtpClient` in C# will do the job. — Charlieface, Aug 15 '22 at 01:39
@Charlieface: I can use C# or powershell but the requirement is to use the SQL Server's email profile. The customer does not want to give up the credentials for the email account. — BlueTube, Aug 16 '22 at 14:19

Strange characters at the beginning of query results when attaching HTML file to email using msdb.dbo.sp_send_dbmail

0 Answers0