Post reply

Issues with msdb.dbo.sp_send_dbmail procedure

  • September 15, 2013 at 3:42 am

    #395486

    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

    [...]

    @productvarchar(64),

    @Subjvarchar(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

  • September 15, 2013 at 8:42 pm

    #1650308

    What are you calling "whitespace"? Precisely what is in the variable when you get the error? Is it a NULL?

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • September 16, 2013 at 2:43 am

    #1650337

    have you checked @product for any special charaters

    like linefeed etc.?

  • September 16, 2013 at 4:58 am

    #1650365

    i will second Jeff here. Do the NULL handling in all the vairables (ISNULL(@variable, ''))

    Also Before sendin it to DBmail

    Print the Text for @Subj = 'New ' + @ListType + ' Release in ''' + @product + ' ' + @Country + ''' ' + @StatusChange

    and copy it to Notepad. See if you can smell soething fishy there (Any special character or EXTRA White spaces )

    -------Bhuvnesh----------
    I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

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

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