SQL Server sp_send_dbmail prevent html encoding in body

Question

How can I prevent encoding within the body of an email sent using SQL Server's sp_send_dbmail?

Example:

EXEC msdb.dbo.sp_send_dbmail
     @recipients='[email protected]',
     @subject = 'SQL email test',
     @body = 'this+that',
     @body_format = 'HTML'

The html source of the email that I receive looks like this:

this`+`that

and the body looks fine:

this+that

However, what if I want the SOURCE to still have the + instead of the + ?

Long back story on why I need to do this, but solving this would help me immensely. Is there a way to "escape" the + in my body parameter? Or any other idea on how to prevent this encoding?

Answer 1

You can try doing a simple XML hack:

DECLARE @source VARCHAR(255) = 'this`+`that';

DECLARE @body VARCHAR(255)= CAST ( CAST ( 'this`+`that' AS XML ) AS VARCHAR(255) );
SELECT @body AS body;

Returns

+-------------+
|    body     |
+-------------+
| this`+`that |
+-------------+

This will not however remove your backticks. You can add a call to REPLACE if needed:

DECLARE @body VARCHAR(255)= REPLACE ( CAST ( CAST ( 'this`+`that' AS XML ) AS VARCHAR(255) ), '`', '' );

Which results in:

+-----------+
|   body    |
+-----------+
| this+that |
+-----------+