October 9, 2007 at 6:06 am
I've written a stored procedure to send daily notifications. It
queries a table that lists e-mail addresses and messages that need to
be sent and then creates and sends individual e-mails with database
mail.
**Query to get e-mail address and message
WHILE (currentRecipient <= maxRecipients)
BEGIN
**Set @To, @Title and @Message
EXEC sp_send_dbmail
@recipients=@To,
@subject = @Title,
@body = @Message,
@body_format = 'TEXT';
END
This all works well, but I need a method to notify someone when the
messages fail. If an address is bad, the mail isn't sent at all it
just gets logged in the database mail log. If the message bounces
back, it is returned to the -noreply used as the From address. Is
there any way to send bounce backs and send failures to another
address without using that as the From address? Thanks in advance for
any assistance you can offer.
-Sean
October 9, 2007 at 3:16 pm
According to BOL you could use the return value of the sp to determine if the message succeded. Like this:
Declare @RetVal Integer
Exec @RetVal = sp_send_dbmail @recipients=@To, @subject = @Title, @body = @Message, @body_format = 'TEXT';
If @Retval = 1 -- 0 means success, 1 means failure
Begin
{Insert code handling failure here}
End
Of course this procedure inserts the message in a Service Broker queue and an outside process actually sends the mail so an invalid email address could still fail after the sp is successful. If you check BOL under
"How to: Check the Status of E-Mail Messages Sent With Database Mail (Transact-SQL)". You should be able to create a process that will alert you of failed messages.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply