July 23, 2009 at 10:36 am
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,
July 23, 2009 at 1:49 pm
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
July 23, 2009 at 2:23 pm
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
July 23, 2009 at 3:49 pm
Well, in theory you should be able to find out because there are 4 vales for sent_status:
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 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy