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