sp_send_dbmail - non-ASCII Characters in Email Subject

Question

sp_send_dbmail - non-ASCII Characters in Email Subject

jdarsley

Mr or Mrs. 500

Points: 541
More actions
January 23, 2009 at 10:09 am

#199276

Hi Folks,
I've been trying out sp_send_dbmail on a test server, and am having problems in sending out an email with east European accented characters in the message subject.
When the message arrives in my inbox, it will have lost some of the accents - for example, the Czech word "Zaměstnanecký" (here's hoping that displays correctly when it gets posted to the forum here) becomes "Zamestnanecký". Note, this only applies to the message subject - characters in the message body are fine.
Everything appears ok from SQL Server's perspective. Querying msdb.dbo.sysmail_sentitems shows the subject with the words correctly accented, so it would appear that the damage is done further down the line. I have heard that intervening mail servers might strip out non-ASCII accents, but in this case I don't think that's the case, as I can send out the same message from Outlook, via the same mail server, and the subject arrives intact.
We are running SQL 2008 Standard Edition, build 10.0.1600.22, SQL_Latin1_General_CP1_CI_AS.
Does anyone have any ideas what might be happening here?
Cheers
John

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Ken Johnson-162971 Ten Centuries Points: 1296 More actions · Answer 1

Are you using nvarchar as you store the value before using it in sp_send_dbmail (or including "N" prior to the name if hard-coding it: N'Zamestnanecký')?

Here is a quick sample that just prints instead of emailing:

DECLARE @name varchar(128)

DECLARE @name1 nvarchar(128)

DECLARE @name2 nvarchar(128)

SET @name = 'Zamestnanecký'

SET @name1 = 'Zamestnanecký'

SET @name2 = N'Zamestnanecký'

PRINT @name -- this will not include the e

PRINT @name1 -- this will not include the e

PRINT @name2 -- this will include the e

I was able to reproduce your results on SQL Server 2008 with the following query (note the "N" prior to the string for @body but not for @subject):

EXEC msdb..sp_send_dbmail

@recipients = ' '

, @subject = 'Zamestnanecký'

, @body = N'Zamestnanecký'

jdarsley Mr or Mrs. 500 Points: 541 More actions · Answer 2

Hi Ken, thanks for the response.

I was pretty confident that I was passing the accented characters as Unicode, as they showed up as expected within sysmail_sentitems. And having looked at this some more over the weekend I've come to the conclusion that it isn't SQL Server that's getting it wrong.

I'd originally tried sending the test emails to various work and home accounts. The one common factor in viewing the messages received was my copy of Outlook here at work, which shows certain characters without the accents. However, when I looked at those same messages via Outlook at home, the accents were all present and correct.

That leaves me wondering what's wrong with my Outlook settings here at work. It's definitely set to use Unicode, and seems happy enough to display those characters if it was actually the sending client, but not when the message originated from SQL Server.

However, unless some expert on Outlook just happens to wander past here, I don't suppose this is the forum for that.

Thanks again for the feedback.

Cheers

John

Ken Johnson-162971 Ten Centuries Points: 1296 More actions · Answer 3

That's an interesting "feature".

This may be a longshot, but which version of Outlook are you using at work?

The MS Office web site has a Unicode information page that states

"Earlier versions of Outlook provided support for multilingual Unicode data in the body of Outlook items. However, Outlook data, such as the To and Subject lines of messages and the ContactName and BusinessTelephoneNumber properties of contact items, were limited to characters defined by your system code page. This limitation is no longer the case in Microsoft Office Outlook 2003 and Microsoft Office Outlook 2007, provided Outlook is running in Unicode mode with an Exchange account."

http://office.microsoft.com/en-us/outlook/HA101659691033.aspx

Have fun!

KenJ

jdarsley Mr or Mrs. 500 Points: 541 More actions · Answer 4

Hi Ken,

Thanks, that was worth checking - but unfortunately we're using both Outlook 2003 & 2007, in Unicode mode. The strange thing is that if I send an email directly from SQL Server to a work address, the accent is removed, but if I send it from SQL Server to my home address, and then forward it to work, the accent arrives intact.

I'm going to have to ponder this one for a while.

Cheers

John