SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Send DB mail error handling


Send DB mail error handling

Author
Message
Yuri55
Yuri55
SSCommitted
SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)

Group: General Forum Members
Points: 1916 Visits: 2056
Created script to send mails using sp_send_dbmail- working like a charm.
Now searching for a way to get result code of sent mail (like Success = Recipient got it,
Failure = Did not get regardless of the reason).
I mean SP return codes 0 (success) or 1 (failure) refer to correct mail Profile, not missing Recipient, etc.

Frankly not sure this is possible as it looks like outside Sql Server authority/responsibility?!

Really appreciate any help. Thanks,
Jack Corbett
  Jack Corbett
SSC-Forever
SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)

Group: General Forum Members
Points: 45139 Visits: 14925
Yuri,

Database Mail uses Service Broker behind the scenes so basically sp_send_dbmail queues the messages and then Service Broker processes the messages. So as long as the mail item is placed in the queue you will not get a mail sent or failed message.

sp_send_dbmail does return @mailitem_id as an output parameter so you can then run a query like this:


SELECT
SA.mailitem_id,
SA.recipients,
SA.copy_recipients,
SA.blind_copy_recipients,
SA.subject,
SA.sent_status,
SA.sent_date,
SEL.event_type,
SEL.log_date,
SEL.description
FROM
dbo.sysmail_allitems AS SA LEFT JOIN
dbo.sysmail_event_log AS SEL ON
SA.mailitem_id = SEL.mailitem_id
Where
SA.mailitem_id = @mailitem_id



Or you could do a job/report that checks a time range.



Jack Corbett

Applications Developer

Don't let the good be the enemy of the best. -- Paul Fleming
At best you can say that one job may be more secure than another, but total job security is an illusion. -- Rod at work

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
How to Post Performance Problems
Crosstabs and Pivots or How to turn rows into columns Part 1
Crosstabs and Pivots or How to turn rows into columns Part 2
Yuri55
Yuri55
SSCommitted
SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)

Group: General Forum Members
Points: 1916 Visits: 2056
Jack, thanks a lot for your response.

If I got you there is no way to control/check success/failure of email sending
(I just used your query to check "sent_status" to "dummy" recipient- as expected no error indication).

Probably only Mail Server (we used during mail Profile configuration) "knows" about sending result but this is different story.

Cheers, Yuri
Jack Corbett
  Jack Corbett
SSC-Forever
SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)

Group: General Forum Members
Points: 45139 Visits: 14925
Well, in theory you should be able to find out because there are 4 vales for sent_status:


  • unsent

  • sent

  • retrying

  • failed



You should have an error for failed messages. It may take a bit of time because it is an asynchronous operation.



Jack Corbett

Applications Developer

Don't let the good be the enemy of the best. -- Paul Fleming
At best you can say that one job may be more secure than another, but total job security is an illusion. -- Rod at work

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
How to Post Performance Problems
Crosstabs and Pivots or How to turn rows into columns Part 1
Crosstabs and Pivots or How to turn rows into columns Part 2
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search