Send DB mail error handling

  • 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,

  • 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
    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

  • 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

  • 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
    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 4 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply