Issues with msdb.dbo.sp_send_dbmail procedure

• tomasz.jozwiak 61372

  SSC Enthusiast

  Points: 107

  More actions

  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

• Jeff Moden

  SSC Guru

  Points: 1004581

  More actions

  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)
  

• anastke

  SSChasing Mays

  Points: 632

  More actions

  September 16, 2013 at 2:43 am

  #1650337

  have you checked @product for any special charaters

  like linefeed etc.?

• Bhuvnesh

  SSC Guru

  Points: 59351

  More actions

  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;-)