Sp Send Dbmail

How to Change Attachment Encoding in `sp_send_dbmail` for SQL Server

Sending emails with attachments through SQL Server can be a straightforward task. However, if you encounter a problem where the attachment is sent as UCS-2 encoded and you want it to be ANSI or UTF-8 encoded, you’re not alone. Many users have faced similar issues, especially when using the sp_send_dbmail stored procedure in SQL Server 2005. In this blog post, we will discuss the problem and provide a solution to ensure your attachments are encoded correctly.

The Problem

When using the sp_send_dbmail functionality to send SQL query results as attachments, you may find that the results are encoded in UCS-2 format. This format can be problematic for the recipients as it may not be compatible with various applications that expect ANSI or UTF-8 formats.

Example Scenario

In your scenario, the query you run with sp_send_dbmail is as follows:

EXEC msdb.dbo.sp_send_dbmail
    @recipients = 'temp@example.com',
    @query = 'DECLARE @string_to_trim varchar(60);SET @string_to_trim = ''1234''; select rtrim(@string_to_trim), ''tom''',
    @query_result_header=0,
    @subject = 'see attach',
    @body= 'temp body',
    @profile_name= N'wksql01tAdmin',
    @body_format = 'HTML',
    @query_result_separator = ',',
    @query_attachment_filename = 'results.csv',
    @query_no_truncate = '0',
    @attach_query_result_as_file = 1

Observed Issue

Despite implementing the above SQL code, your results are still sent as UCS-2 encoded and you mentioned some comments about a fix being available in SQL Server 2005 SP2, but it appears that this is not the case in your situation.

The Solution

While there may be limited options for changing the encoding directly through sp_send_dbmail, you can work around this issue by using the BCP (Bulk Copy Program) utility.

Using BCP for Proper Encoding

Export Data to a Flat File: Use BCP to export the results of your query into a flat file. BCP allows you to specify the encoding format during the export process.
```
bcp "SELECT RTRIM(@string_to_trim), 'tom'" queryout "C:\path\to\results.csv" -c -C 65001 -T
```
- The -c option indicates character mode.
- The -C 65001 option specifies UTF-8 encoding.
- The -T option uses a trusted connection to SQL Server.
Attach the Flat File: Once the file is generated with the desired encoding, you can then attach this file to your email using the conventional methods. This can be managed through a script or via the SQL Server Agent jobs where you can execute this BCP command and follow it with sp_send_dbmail to send the email.

Summary of Steps

Execute a BCP command to dump your query results into a .csv file with UTF-8 encoding.
Use sp_send_dbmail to send an email with the .csv attachment.

Conclusion

Though it may seem daunting at first, resolving the UCS-2 encoding issue with your sp_send_dbmail attachments is manageable with the use of BCP. By following the steps outlined above, you can ensure your attachments are encoded properly, making it easier for your recipients to access and utilize the information provided.

If you’re still facing issues or have any further questions, feel free to reach out!

How to Change Attachment Encoding in sp_send_dbmail for SQL Server