SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Issue with sp_send_dbmail procedure
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

zimmonn
Starting Member

1 Posts

Posted - 09/15/2013 :  05:33:37  Show Profile  Reply with Quote
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

djj55
Constraint Violating Yak Guru

USA
337 Posts

Posted - 09/16/2013 :  10:22:20  Show Profile  Reply with Quote
Have you use PRINT @Subj to check what the "whitespace" value is without sending an email?

djj
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000