I am sending emails using msdb.dbo.sp_send_dbmail. Sometimes the emails do not send. I have got the list of emails which have failed.
SELECT TOP 10 * from msdb.dbo.sysmail_event_log
log_id event_type log_date process_id mailitem_id account_id last_mod_date last_mod_user
9022 error 50:15.9 5608 20428 NULL 50:15.9 sa
9023 error 51:23.3 5608 20428 NULL 51:23.3 sa
Now, I want to resend all failed emails again which are available in the sysmail_event_log table.
How can I resend all failed emails?
4条答案
按热度按时间amrnrhlw1#
Use the following query for sending back a failed item.
Or use a CURSOR for each row from msdb.dbo.sysmail_faileditems with the same query
References
resending-failed-emails-through-sp_send_email
CURSOR
fv2wmkja2#
Building from Hybris95's answer, here is a snippet that sends all failed items after a cutoff timestamp without a cursor. Mail profiles are taken in consideration.
jm81lzqq3#
Or you can do what send_dbmail itself does after building the message in source tables (which still exists in case of failed mail), and just push the failed message back onto the service broker send queue, and let it manage all the correct updates.
where @mailitem_id is the id of the message to resend that you have identified.
Just be aware the exact format of the XML Message is an undocumented internal, so may change by SQL version. The indented lines (including misspelled comment!) are pulled straight out of sp_send_dbmail on a SQL 17 server, and are same on 19. I believe they were different on some earlier version, but don't have installs to check.
3pvhb19x4#
I know this is a bit old, but there is a very trim and elegant answer that I use which basically combines the work shown by Mark Lopez and Tla2c into something that actually resends the same mail item (no having to create new mail items, and preserving the original mailitem_id) and avoids the use of cursors (because I know plenty of environments that restrict them, though this performs a pretty cursor-like action)