February 21, 2019 at 5:07 am
Hi,
I have many jobs running in SQL Server for which mail is getting triggered transaction wise. E-mails are maintained in a table and based on that e-mail is getting triggered.
Customer is asking for a list of e-mails for which the mail has not gone to user. It may be for a reason that email id is no longer valid if he resigned from company.
Can any 1 suggest how to take this list please
February 21, 2019 at 6:22 pm
if you are using SQL Database mail, you can query the msdb database:
if the smtp server returns an error, like no such mailbox, you can see it in there.
here's an example of the "latest" failures:
SELECT top 100
mail.send_request_date As SentDate,
sent_status As Reason,
err.[description],
mail.*
FROM [msdb].[dbo].[sysmail_allitems] mail
inner join [msdb].[dbo].[sysmail_event_log] err
ON err.mailitem_id = mail.mailitem_id
WHERE mail.sent_status <> 'sent'
order by mailitem_id desc
Lowell
February 22, 2019 at 3:13 am
Hi,
Thanks for your help!!!!
Yes using SQL Database mail only. I will check the same and let you know for issues if any.
Do you have any idea if this will work if 1 email id is valid and 1 email id is invalid. For example cc email is valid and to email is valid i had seen that sent is available in sysmail_event_log?
February 22, 2019 at 8:39 am
probably not.
usually when you send an email to a number of recipients, for example to: currentlyValid@email.com;nolongerValid@email.com;, the SMTP server accepts the message, and database mail receives the SMTP 200 OK message.
later, the mail server replies back with "no such mailbox" or some other email, which is a reply, and outside of database mail.
you would need some other process to handshake like that to cull invalid emails outside of explicit error messages that are captured in database mail.
Lowell
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply