• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1436
  • Last Modified:

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
0
valkyrie_nc
Asked:
valkyrie_nc
  • 2
1 Solution
 
Ved Prakash AgrawalDatabase Consultant/Performance ArchitectCommented:
HI are you able to send mail through sp_send_dbmail  to outside with the specify smtp mail server in the default profiler.
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.

0
 
valkyrie_ncAuthor Commented:
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.
0
 
valkyrie_ncAuthor Commented:
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@whatever.com',@subject='howdy',@body='whatever'

Works just fine that way!

valkyrie_nc

(Administrator, please delete.)
0
 
CetusMODCommented:
PAQed with points refunded (500)

CetusMOD
Community Support Moderator
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now