SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


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


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

Author
Message
phf
phf
SSC-Enthusiastic
SSC-Enthusiastic (135 reputation)SSC-Enthusiastic (135 reputation)SSC-Enthusiastic (135 reputation)SSC-Enthusiastic (135 reputation)SSC-Enthusiastic (135 reputation)SSC-Enthusiastic (135 reputation)SSC-Enthusiastic (135 reputation)SSC-Enthusiastic (135 reputation)

Group: General Forum Members
Points: 135 Visits: 15
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
Thom A
SSC Guru
SSC Guru (69K reputation)SSC Guru (69K reputation)SSC Guru (69K reputation)SSC Guru (69K reputation)SSC Guru (69K reputation)SSC Guru (69K reputation)SSC Guru (69K reputation)SSC Guru (69K reputation)

Group: General Forum Members
Points: 69493 Visits: 18363
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 :-P

Please always remember to encapsulate your code in IFCode Markup. For example [code=sql] [/code].
Click here to read Jeffs Guide on how to post SQL questions, and get swift and helpful answers from the community
phf
phf
SSC-Enthusiastic
SSC-Enthusiastic (135 reputation)SSC-Enthusiastic (135 reputation)SSC-Enthusiastic (135 reputation)SSC-Enthusiastic (135 reputation)SSC-Enthusiastic (135 reputation)SSC-Enthusiastic (135 reputation)SSC-Enthusiastic (135 reputation)SSC-Enthusiastic (135 reputation)

Group: General Forum Members
Points: 135 Visits: 15
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
Sue_H
SSC Guru
SSC Guru (57K reputation)SSC Guru (57K reputation)SSC Guru (57K reputation)SSC Guru (57K reputation)SSC Guru (57K reputation)SSC Guru (57K reputation)SSC Guru (57K reputation)SSC Guru (57K reputation)

Group: General Forum Members
Points: 57364 Visits: 12736

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
Thom A
SSC Guru
SSC Guru (69K reputation)SSC Guru (69K reputation)SSC Guru (69K reputation)SSC Guru (69K reputation)SSC Guru (69K reputation)SSC Guru (69K reputation)SSC Guru (69K reputation)SSC Guru (69K reputation)

Group: General Forum Members
Points: 69493 Visits: 18363
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 :-P

Please always remember to encapsulate your code in IFCode Markup. For example [code=sql] [/code].
Click here to read Jeffs Guide on how to post SQL questions, and get swift and helpful answers from the community
Ed Wagner
Ed Wagner
SSC Guru
SSC Guru (237K reputation)SSC Guru (237K reputation)SSC Guru (237K reputation)SSC Guru (237K reputation)SSC Guru (237K reputation)SSC Guru (237K reputation)SSC Guru (237K reputation)SSC Guru (237K reputation)

Group: General Forum Members
Points: 237426 Visits: 12035
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.


Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions
Thom A
Thom A
SSC Guru
SSC Guru (69K reputation)SSC Guru (69K reputation)SSC Guru (69K reputation)SSC Guru (69K reputation)SSC Guru (69K reputation)SSC Guru (69K reputation)SSC Guru (69K reputation)SSC Guru (69K reputation)

Group: General Forum Members
Points: 69493 Visits: 18363
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 :-P

Please always remember to encapsulate your code in IFCode Markup. For example [code=sql] [/code].
Click here to read Jeffs Guide on how to post SQL questions, and get swift and helpful answers from the community
phf
phf
SSC-Enthusiastic
SSC-Enthusiastic (135 reputation)SSC-Enthusiastic (135 reputation)SSC-Enthusiastic (135 reputation)SSC-Enthusiastic (135 reputation)SSC-Enthusiastic (135 reputation)SSC-Enthusiastic (135 reputation)SSC-Enthusiastic (135 reputation)SSC-Enthusiastic (135 reputation)

Group: General Forum Members
Points: 135 Visits: 15
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
phf
SSC-Enthusiastic
SSC-Enthusiastic (135 reputation)SSC-Enthusiastic (135 reputation)SSC-Enthusiastic (135 reputation)SSC-Enthusiastic (135 reputation)SSC-Enthusiastic (135 reputation)SSC-Enthusiastic (135 reputation)SSC-Enthusiastic (135 reputation)SSC-Enthusiastic (135 reputation)

Group: General Forum Members
Points: 135 Visits: 15
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
Thom A
SSC Guru
SSC Guru (69K reputation)SSC Guru (69K reputation)SSC Guru (69K reputation)SSC Guru (69K reputation)SSC Guru (69K reputation)SSC Guru (69K reputation)SSC Guru (69K reputation)SSC Guru (69K reputation)

Group: General Forum Members
Points: 69493 Visits: 18363
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 :-P

Please always remember to encapsulate your code in IFCode Markup. For example [code=sql] [/code].
Click here to read Jeffs Guide on how to post SQL questions, and get swift and helpful answers from the community
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum








































































































































































SQLServerCentral


Search