May 24, 2017 at 5:47 am
I'm using SQL Server Database Mail configured with 1 retry. The first send attempt produces a row in the msdb.dbo.sysmail_allitems view. If the first send attempt fails a second send attempt is made, which produces another row in that view. The mailitem_id of the two rows differs. Is there a way to correlate the two rows so that I can identify the retry row from the mailitem_id of the first send attempt?
May 25, 2017 at 1:00 pm
chris-howard - Wednesday, May 24, 2017 5:47 AMI'm using SQL Server Database Mail configured with 1 retry. The first send attempt produces a row in the msdb.dbo.sysmail_allitems view. If the first send attempt fails a second send attempt is made, which produces another row in that view. The mailitem_id of the two rows differs. Is there a way to correlate the two rows so that I can identify the retry row from the mailitem_id of the first send attempt?
Are you sure a new row is being added on the retry or after the retry is successful?
Does the first attempt or first row have a status of failed? That would be a different mail item that failed after any retries.
You can also check sysmail_sentitems and sysmail_faileditems as well as the status column in sysmail_allitems to see what emails were sent and what emails failed.
Sue
May 25, 2017 at 2:36 pm
i believe msdb.dbo.sysmail_allitems will only have a single row per mail_id, along with it's current status (sent/unsent/failed)
when a mail does fail, an entry is created in sysmail_event_logselect
mailitem_id,
count(*)
from msdb.dbo.sysmail_event_log
group by mailitem_id
having count(*) > 1;
SELECT * FROM from msdb.dbo.sysmail_event_log
Lowell
June 20, 2017 at 5:05 am
Turns out I was wrong and the replies above are correct - sorry I wasted your time.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply