sp_send_dbmail fails when run from a stored procedure
I have an app I'm moving from SQL Server 2k to 2k5. Several stored procedures in this app use sp_send_dbmail to send emails. I'm finding that I'm getting an error whenever the stored procedure tries to send the email, although using the sp_send_dbmail procedure works fine when run from code (e.g., as a SqlCommand). The error I get is:
The mail could not be sent to the recipients because of the mail server failure. (Sending Mail using Account 1 (2006-06-14T12:01:29). Exception Message: Cannot send mails to mail server. (The specified string is not in the form required for an e-mail address.).)
I've checked, and it's using a valid email address (joe@whatever.com) format. It doesn't matter if I send the email in as a parameter or retrieve it within the stored procedure, or even type it in as a hard-coded varchar value; same error message results. There's no problem with the default profile; it sends emails just fine if you test it, and if an email is sent from within code.
Any idea why it's throwing such a fit about the format of the email address, and what I can do?
Thanks!
valkyrie_nc
The mail could not be sent to the recipients because of the mail server failure. (Sending Mail using Account 1 (2006-06-14T12:01:29). Exception Message: Cannot send mails to mail server. (The specified string is not in the form required for an e-mail address.).)
I've checked, and it's using a valid email address (joe@whatever.com) format. It doesn't matter if I send the email in as a parameter or retrieve it within the stored procedure, or even type it in as a hard-coded varchar value; same error message results. There's no problem with the default profile; it sends emails just fine if you test it, and if an email is sent from within code.
Any idea why it's throwing such a fit about the format of the email address, and what I can do?
Thanks!
valkyrie_nc
ASKER
My profile is okay, 'cause I can send to internal and external addresses using the Test E-mail feature, but I get the error message any time I use sp_send_dbmail. It seems to be the sp_send_dbmail procedure that is at fault, and not the fact that it's used in a stored procedure as I previously conjectured. <blush>
Could it be something to do with SQL2k5 being on a virtual server? The same setup works fine on my test box, which is not a virtual server.
Could it be something to do with SQL2k5 being on a virtual server? The same setup works fine on my test box, which is not a virtual server.
ASKER
Okay, figured it out. I wasn't defining the parameters for the procedure correctly. Instead of just listing the values, I needed to put the parameter name in front:
EXEC msdb..sp_send_dbmail @recipients='whatever@what
Works just fine that way!
valkyrie_nc
(Administrator, please delete.)
EXEC msdb..sp_send_dbmail @recipients='whatever@what
Works just fine that way!
valkyrie_nc
(Administrator, please delete.)
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
please check your profiler which is you are using for sending mail.
because i am using that procedure to send but i have no problem to send mail when i used to my local machine as smtp mail server.