none
Problem with sending emails using msdb.dbo.sp_send_dbmail procedure (variable as the subject)

    Question

  • Hi All,

    For the last several days I'm trying to resolve issue with sending emails by following procedure (I've ommited 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

    Sunday, September 15, 2013 10:01 AM

Answers

  • Can perhaps @Product contain something like a nul terminated string (like you terminate string in C family language with a nul)? I'm thinking that sometimes programmers keep these nul at end of string when written into the database and that can cause problems in SQL in general, and possibly this is what your mail server is complaining about?

    Tibor Karaszi, SQL Server MVP | web | blog

    Sunday, September 15, 2013 5:21 PM
    Moderator
  • Tomas,

    I think Tibor has the solution for you. I tried sending e-mail message with CR LF in the middle of the @subject and received error in Database Mail Log.

    declare @subject nvarchar(255)
    
    set @subject = 'test10' + char(10) + char(13) + 'test2'
    
    exec msdb.dbo.sp_send_dbmail
    	@profile_name = 'COMM-SRV-DB-01 SQL2012'
    	, @recipients = 'dean.savovic@comminus.hr'
    	, @subject = @subject
    

    Date		15.9.2013. 19:46:29
    Log		Database Mail (Database Mail Log)
    
    Log ID		1197
    Process ID		2184
    Mail Item ID	3056
    Last Modified	15.9.2013. 19:46:29
    Last Modified By	sa
    
    Message
    The mail could not be sent to the recipients because of the mail server failure. (Sending Mail using Account 4 (2013-09-15T19:46:29). Exception Message: Cannot send mails to mail server. (The specified string is not in the form required for a subject.).
    )

    Try removing CR LF characters:

    declare @subject nvarchar(255)
    
    set @subject = replace (replace ('test10' + char(10) + char(13) + 'test2', char(10), ''), char(13), '');
    
    exec msdb.dbo.sp_send_dbmail
    	@profile_name = 'COMM-SRV-DB-01 SQL2012'
    	, @recipients = 'dean.savovic@comminus.hr'
    	, @subject = @subject
    

    Although you could have some other special characters that are making sp_send_dbmail report an error. You can use ASCII function to see what do you have in character that is causing the error.


    Regards, Dean Savović

    Sunday, September 15, 2013 5:53 PM

All replies

  • Can you try SPACE() function?

    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Blog: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance

    Sunday, September 15, 2013 11:04 AM
    Answerer
  • Hi Tomasz,

    It seems you are getting issue when your @Product is having NULL value because  select 'ABC' + NULL = NULL. If you add any string to null, it will become NULL. You can use ISNULL function to resolve your issue. whenever null comes, it should convert to blank.

     

    SELECT @Subj = 'New ' + @ListType + ' Release in ''' + ISNULL(@Product,'') + ' ' + @Country + ''' ' + @StatusChange


    Hope this will solve your issue.

    Thanks,

    Vishal

    Sunday, September 15, 2013 11:32 AM
  • It didn't help. The issue is if there is a whitespace in the value than the issue appears so:

    If the value for @Product = 'orange' there is no issue with sending email, but on the other hand if the value for @Product = 'orange white' an email will not be sent. From my understanding that is the issue while providing data from SQL to exchange... (the whitespace is a an issue here).

    Anyway if there is any whitespace in the value the issue occures.

    Thanks in advance!

    ps. I will try to sniff a little bit using wireshark to check for more data.

    Sunday, September 15, 2013 1:31 PM
  • Can perhaps @Product contain something like a nul terminated string (like you terminate string in C family language with a nul)? I'm thinking that sometimes programmers keep these nul at end of string when written into the database and that can cause problems in SQL in general, and possibly this is what your mail server is complaining about?

    Tibor Karaszi, SQL Server MVP | web | blog

    Sunday, September 15, 2013 5:21 PM
    Moderator
  • Tomas,

    I think Tibor has the solution for you. I tried sending e-mail message with CR LF in the middle of the @subject and received error in Database Mail Log.

    declare @subject nvarchar(255)
    
    set @subject = 'test10' + char(10) + char(13) + 'test2'
    
    exec msdb.dbo.sp_send_dbmail
    	@profile_name = 'COMM-SRV-DB-01 SQL2012'
    	, @recipients = 'dean.savovic@comminus.hr'
    	, @subject = @subject
    

    Date		15.9.2013. 19:46:29
    Log		Database Mail (Database Mail Log)
    
    Log ID		1197
    Process ID		2184
    Mail Item ID	3056
    Last Modified	15.9.2013. 19:46:29
    Last Modified By	sa
    
    Message
    The mail could not be sent to the recipients because of the mail server failure. (Sending Mail using Account 4 (2013-09-15T19:46:29). Exception Message: Cannot send mails to mail server. (The specified string is not in the form required for a subject.).
    )

    Try removing CR LF characters:

    declare @subject nvarchar(255)
    
    set @subject = replace (replace ('test10' + char(10) + char(13) + 'test2', char(10), ''), char(13), '');
    
    exec msdb.dbo.sp_send_dbmail
    	@profile_name = 'COMM-SRV-DB-01 SQL2012'
    	, @recipients = 'dean.savovic@comminus.hr'
    	, @subject = @subject
    

    Although you could have some other special characters that are making sp_send_dbmail report an error. You can use ASCII function to see what do you have in character that is causing the error.


    Regards, Dean Savović

    Sunday, September 15, 2013 5:53 PM
  • Hello All,

    @Tibor/Dean,

    Thanks a lot for Your input. Indeed the issue was related to special characters CR LF. I've amended procedure (added REPLACE function). And now everything works fine.

    Once again thanks for that smart solution!

    Tomasz

    Monday, September 16, 2013 8:23 AM