Reporting Services: Special characters used in email addresses in subscription cause an error

  • phf

    SSC Veteran

    Points: 211

    Hi
    I'm struggling with a rare problem in the mail address in subscriptions in Reporting Services.
    Here in Denmark we, like in many other countries, have some special characters like æøå. I have noticed that subscriptions fail when the name of the person, or the mail address contain one of these characters. It could be like "Jørgen Ørum <jørgen@ærø.dk>". I don't know if he exist.

    These characters are fully legal to use, but it seems that SSRS does not yet support them. Is gives this error: 
    "Failure sending mail: The specified string is not in the form required for an e-mail address. Mail will not be resent."
    I have tried to HTML-encode the string, with no luck.
    That is rather critical, and maybe there is someone out there who found a solution to this problem, and Google simply gave no hint.

  • Thom A

    SSC Guru

    Points: 98219

    Just to check, are you entering "Jørgen Ørum <jørgen@ærø.dk>" as the email address? You only need to enter the email address, separated by semi colons. For example: jørgen@ærø.dk;jeroen@ærø.dk.

    Thom~

    Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.

  • phf

    SSC Veteran

    Points: 211

    Hi Thom A

    Even if I leave out the name, and only try to send to an email address like jørgen@ærø.dk, I get the same error.
    Lets say that the domain part of the email address is represented by only normal ascii characters (below ascii 127, like @varmt.dk), but the local part consists of a single special character (like jørgen@), then the error occurs.
    The local part may according to the standard consist of almost any character. Even with quotes around the local part, as recommended, the result is the same error.

  • Sue_H

    SSC Guru

    Points: 89905

    I'm wondering if the issue is with sp_send_dbmail and @recipients being a varchar instead of nvarchar?
    Are any of the addresses in sysmail_allitems - with question marks for the special characters? Not sure if that would happen but worth checking since the column in that view is varchar as well. .

    Sue

  • Thom A

    SSC Guru

    Points: 98219

    I've not been able to find anything on this topic (thus no solution sorry). It might be worth raising a connect item, if it is a "feature" then someone might have a workaround. Otherwise there may be a "simple" solution.

    Thom~

    Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.

  • Ed Wagner

    SSC Guru

    Points: 286955

    I think Sue hit the nail on the head.  If you look at https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-send-dbmail-transact-sql, you'll see that the @recipients parameter is definitely a varchar.  There are other parameters that are nvarchar and I can tell you from experience that they do work properly.  Specifically, I've used subject and body in this procedure as Chinese and it works fine.  The recipients parameters, however, are varchar, so they wouldn't support Unicode characters.

  • Thom A

    SSC Guru

    Points: 98219

    Ed Wagner - Saturday, May 6, 2017 3:13 PM

    I think Sue hit the nail on the head.  If you look at https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-send-dbmail-transact-sql, you'll see that the @recipients parameter is definitely a varchar.  There are other parameters that are nvarchar and I can tell you from experience that they do work properly.  Specifically, I've used subject and body in this procedure as Chinese and it works fine.  The recipients parameters, however, are varchar, so they wouldn't support Unicode characters.

    I might be mistaken, but I don't think the characters that the OP has provided would be lost on a varchar parameter. At least on my my instance the below SQL returns:
    string
    ------------------------------
    Jørgen Ørum <jørgen@ærø.dk>
    aß?

    CREATE TABLE #Test (string varchar(30));
    GO

    INSERT INTO #Test
    VALUES
      ('Jørgen Ørum <jørgen@ærø.dk>'), --OPs example email
      ('αβγ'); -- Some more random Greek characters.
    GO

    SELECT *
    FROM #test;

    DROP TABLE #Test;
    GO

    So the only character that was "lost" was γ. I can't see why a varchar parameter would therefore be unhappy to receive the characters in the OPs post.

    Thom~

    Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.

  • phf

    SSC Veteran

    Points: 211

    Hi Thom

    You are right. All the characters can be represented within 8-bits, no unicode is needed.
    But if we expect a string to be unicode, then all special characters are above ascii 127, and must be represented by 2 bytes. 
    You write about "raising a connect item" .. what is that?

    Have any of you tried to make a subscription on your local installation, to a fake address with a special character (any above ascii 127) to see what happens?
    The reason why I ask, is that I have tested this on a virtual machine I use for development. There I have a mailserver installed, that saves the mail to disk, in stead of sending it away. When I try to send to our friend jørgen, it actually send it without an error. But when I open the mail to see what is inside it, the address has been changed from jørgen@ærø.dk to jxrgen@frx.dk. I wonder if the mailserver is causing me trouble. Although the reason for opening this thread was the same error on two different real life installations both giving an error.

  • phf

    SSC Veteran

    Points: 211

    This morning I have tried to send a subscription to our friend, from a hand full of customers different installations. They all fail, with the exact same error.
    How do I report this bug to M$...??

  • Thom A

    SSC Guru

    Points: 98219

    You can create a Connect item here: https://connect.microsoft.com/SQLServer/Feedback

    Thom~

    Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.

  • Ed Wagner

    SSC Guru

    Points: 286955

    Thom A - Sunday, May 7, 2017 10:09 AM

    Ed Wagner - Saturday, May 6, 2017 3:13 PM

    I think Sue hit the nail on the head.  If you look at https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-send-dbmail-transact-sql, you'll see that the @recipients parameter is definitely a varchar.  There are other parameters that are nvarchar and I can tell you from experience that they do work properly.  Specifically, I've used subject and body in this procedure as Chinese and it works fine.  The recipients parameters, however, are varchar, so they wouldn't support Unicode characters.

    I might be mistaken, but I don't think the characters that the OP has provided would be lost on a varchar parameter. At least on my my instance the below SQL returns:
    string
    ------------------------------
    Jørgen Ørum <jørgen@ærø.dk>
    aß?

    CREATE TABLE #Test (string varchar(30));
    GO

    INSERT INTO #Test
    VALUES
      ('Jørgen Ørum <jørgen@ærø.dk>'), --OPs example email
      ('αβγ'); -- Some more random Greek characters.
    GO

    SELECT *
    FROM #test;

    DROP TABLE #Test;
    GO

    So the only character that was "lost" was γ. I can't see why a varchar parameter would therefore be unhappy to receive the characters in the OPs post.

    Thom, you're absolutely right.  I thought the æ character required Unicode.  That's what I get for posting from home without verifying it.  Thanks for catching and correcting it.

Viewing 11 posts - 1 through 11 (of 11 total)

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