TSQL - msdb.dbo.sp_send_dbmail procedure with variable issue

Question

For the last several days I'm trying to resolve issue with sending emails by following procedure (I've omitted some parts):

 DECLARE     
    [...]    
    @Product            varchar(64),    
    @Subj           varchar(255),    
    [...]    
    SELECT @Subj = 'New ' + @ListType + ' Release in ''' + @Product + ' ' + @Country + ''' ' +  @StatusChange    
    [...]    
    EXEC msdb.dbo.sp_send_dbmail   
    @recipients         = @EMail,   
    @copy_recipients    =  @CCMail,  
    @subject            = @Subj   

    [...]

If in @Product variable is whitespace (from query from other table) i.e oranges (white) the email is not being sent with error message as follow:
"The mail could not be sent to the recipients because of the mail server failure. (Sending Mail using Account Exception Message: Cannot send mails to mail server. (The specified string is not in the form required for a subject.).)". It seems that the message cannot be sent if there is a whitespace in the variable, but on the other hand if I change query:

SELECT @Subj = 'New ' + @ListType + ' Release in ''' + ' oranges (white) ' + ' ' + @Country + ''' ' + @StatusChange

No problem with sending email. Also if I assign the string directy to @subject there is no issue with sending emails too. If there is not white spaces in variable @Product everything is working fine...
I've already tried several different approaches to resolve that issue but still no success (and reviewed a tons of entries on different forums...):
1. On one of the forums I found to check SQLAgent entry regarding account for sending email - didn't resolve the issue
2. Restarting server/services - didn't help
3. Do a cast on @product variable Cast(@product as CHAR(25)) the same issue occures
4. Assign the @subj variable to different variable and the latter one use as the @subject - didn't help

OS: win 2008 std x86
SQL: 2008 std x86 (with all current updates)
Messaging server: Exchange 2010 (with all current sp's and updates)
Telnet: working fine...
No problem to send test email. No other issues from SQL end...

Thanks for Your help on that!

Best Regards,

Tomasz

user2780928 · Accepted Answer · 2013-09-16 08:33:55Z

1

The issue was related to special characters (ASCII 10,13).

I've used REPLACE function to change it in one of the variables.

Wouldn't be possible without help http://social.msdn.microsoft.com/Forums/sqlserver/en-US/e20ccd3b-7320-4364-9abb-19b3d1a6f71b/problem-with-sending-emails-using-msdbdbospsenddbmail-procedure-variable-as-the-subject?prof=required

Thanks!

answered Sep 16, 2013 at 8:33

user2780928

213 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

David Coster Over a year ago

Really helped me! I had a string where the bad character occurred in 224th position. I was thinking that the length of the string might be the problem!!

Collectives™ on Stack Overflow

TSQL - msdb.dbo.sp_send_dbmail procedure with variable issue

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related