Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

sp_send_dbmail fails when run from a stored procedure

Posted on 2006-06-14
5
1,392 Views
Last Modified: 2007-12-19
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
Comment
Question by:valkyrie_nc
  • 2
5 Comments
 
LVL 11

Expert Comment

by:Ved Prakash Agrawal
ID: 16904076
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
 
LVL 9

Author Comment

by:valkyrie_nc
ID: 16904265
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
 
LVL 9

Author Comment

by:valkyrie_nc
ID: 16904904
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
 

Accepted Solution

by:
CetusMOD earned 0 total points
ID: 17631588
PAQed with points refunded (500)

CetusMOD
Community Support Moderator
0

Featured Post

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed

856 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question