SQL Server how I can resend all failed emails (msdb.dbo.sp_send_dbmail)

xzabzqsa  于 2023-04-10  发布在  其他
关注(0)|答案(4)|浏览(88)

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?

amrnrhlw

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

DECLARE @to        varchar(max)
DECLARE @copy    varchar(max)
DECLARE @title    nvarchar(255)
DECLARE @msg    nvarchar(max)
SELECT @to = recipients, @copy = copy_recipients, @title = [subject], @msg = body
FROM msdb.dbo.sysmail_faileditems
WHERE mailitem_id =  56299
EXEC msdb.dbo.sp_send_dbmail
@recipients = @to,
@copy_recipients = @copy,
@body = @msg,
@subject = @title,
@body_format = 'HTML';

References

resending-failed-emails-through-sp_send_email
CURSOR

fv2wmkja

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.

DECLARE @MailitemId INT = 0
DECLARE @Cutoff DATE = CAST(GETDATE() AS DATE)

WHILE (1 = 1) 
BEGIN  
    SELECT TOP 1 @MailitemId = mailitem_id 
    FROM msdb.dbo.sysmail_faileditems
    WHERE
        mailitem_id > @MailitemId
        AND send_request_date > @Cutoff
    ORDER BY mailitem_id

    IF @@ROWCOUNT = 0 BREAK;

    DECLARE @to VARCHAR(MAX)
    DECLARE @copy VARCHAR(MAX)
    DECLARE @title NVARCHAR(255)
    DECLARE @msg NVARCHAR(MAX)
    DECLARE @profile_name NVARCHAR(MAX),
        @file_attachments NVARCHAR(MAX),
        @attach_query_result_as_file BIT 

    SELECT @profile_name = p.name, @to = recipients, @copy = copy_recipients, @title = [subject], @msg = body, @file_attachments = i.file_attachments, @attach_query_result_as_file = i.attach_query_result_as_file
    FROM msdb.dbo.sysmail_faileditems AS i LEFT OUTER JOIN msdb.dbo.sysmail_profile AS p
    ON p.profile_id = i.profile_id
    WHERE
        mailitem_id = @MailitemId

    EXEC msdb.dbo.sp_send_dbmail
        @profile_name = @profile_name,
        @recipients = @to,
        @copy_recipients = @copy,
        @body = @msg,
        @subject = @title,
        @body_format = 'HTML',
        @file_attachments = @file_attachments,
        @attach_query_result_as_file = @attach_query_result_as_file

END
jm81lzqq

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.

Declare @sendmailxml        VARCHAR(max), @mailitem_id               INT 

    -- Create the primary SSB xml maessage
    SET @sendmailxml = '<requests:SendMail xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://schemas.microsoft.com/databasemail/requests RequestTypes.xsd" xmlns:requests="http://schemas.microsoft.com/databasemail/requests"><MailItemId>'
                        + CONVERT(NVARCHAR(20), @mailitem_id) + N'</MailItemId></requests:SendMail>'

    -- Send the send request on queue.
    EXEC sp_SendMailQueues @sendmailxml

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.

3pvhb19x

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)

-- Resend failed Database Mail messages (good for resending failed emails after a previously broken SMTP connection has been restored)

DECLARE @StartResendingFromDate DATE = CAST(GETDATE() AS DATE)  --  Only resend failed emails from today, but you can manually put a date further back.

DECLARE @sendmailxml VARCHAR(max), @MailItemId INT=0
WHILE (1=1)
BEGIN
    SELECT TOP 1 @MailItemId=mailitem_id
        FROM msdb.dbo.sysmail_faileditems
        WHERE send_request_date > @StartResendingFromDate      -- only send things that failed after the resend date
            AND mailitem_id > @MailItemId         -- let's not try to re-queue the same message over & over if it takes a moment for DB Mail to process the queue
        ORDER BY mailitem_id;
    IF @@ROWCOUNT = 0 BREAK;
    SET @sendmailxml = '<requests:SendMail xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://schemas.microsoft.com/databasemail/requests RequestTypes.xsd" xmlns:requests="http://schemas.microsoft.com/databasemail/requests"><MailItemId>' + CONVERT(NVARCHAR(20), @mailitem_id) + N'</MailItemId></requests:SendMail>'
    EXEC msdb.dbo.sp_SendMailQueues @sendmailxml
END

相关问题