Resend failed emails from Database Mail Stored Procedure

Today at work we had an issue with our mail servers that is still being looked at. The problem has caused any email sent from SQL Server Database mail to an external address to fail to send. I therefore needed a way to resend these emails once the problem was fixed. I googled ,found and amended a stored procedure which does the trick. I include it below for reference. Note you can pass in a test email address (i.e. your own) to test the resend without actually sending the unsent emails to the actual recipients.

USE [msdb]
GO

/****** Object:  StoredProcedure [dbo].[sysmail_resend_timeout]    Script Date: 09/18/2012 16:48:10 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[sysmail_resend_timeout]
	@Test bit,
	@TestRecipientEmail varchar(100)
AS
    BEGIN
        SET NOCOUNT ON

        DECLARE SYSMAIL_LOG_RESEND_CURSOR CURSOR READ_ONLY
        FOR
             SELECT DISTINCT
                    l.mailitem_id ,
                    p.name ,
                    m.recipients ,
                    m.subject ,
                    m.body_format ,
                    m.body,
                    m.file_attachments,
                    m.attachment_encoding,
                    m.copy_recipients,
                    m.blind_copy_recipients,
                    m.from_address
             FROM    msdb.dbo.sysmail_log l WITH ( NOLOCK )
                    JOIN msdb.dbo.sysmail_mailitems m WITH ( NOLOCK ) ON m.mailitem_id = l.mailitem_id
                    JOIN msdb.dbo.sysmail_profile p WITH ( NOLOCK ) ON p.profile_id = m.profile_id
            WHERE   l.event_type = 3
                    AND m.sent_status = 2
					AND l.mailitem_id > 6814
            ORDER BY l.mailitem_id

        OPEN SYSMAIL_LOG_RESEND_CURSOR

        WHILE ( 1 = 1 )
            BEGIN
                DECLARE @mailitem_id INT ,
                    @profile_name NVARCHAR(128) ,
                    @recipients VARCHAR(MAX) ,
                    @subject NVARCHAR(255) ,
                    @body_format VARCHAR(20) ,
                    @body NVARCHAR(MAX),
                    @file_attachments NVARCHAR(MAX),
                    @attachment_encoding VARCHAR(20),
                    @copy_recipients VARCHAR(MAX),
                    @blind_copy_recipients VARCHAR(MAX),
                    @from_address VARCHAR(MAX)

                FETCH NEXT FROM SYSMAIL_LOG_RESEND_CURSOR INTO @mailitem_id, @profile_name, @recipients, @subject, @body_format, @body,
                 @file_attachments,
                    @attachment_encoding,
                    @copy_recipients,
                    @blind_copy_recipients,
                    @from_address

                IF NOT @@FETCH_STATUS = 0
                    BEGIN
                        BREAK
                    END

                PRINT 'SENDING MAIL: ' +  CONVERT(VARCHAR, GETDATE(), 121) + CHAR(9) + CONVERT(VARCHAR, @mailitem_id) + CHAR(9) + @recipients

				IF ISNULL(@Test,0) = 1
				BEGIN
					SET @recipients = @TestRecipientEmail
					SET @copy_recipients = @recipients
					SET @blind_copy_recipients = @recipients
				END

					EXEC  msdb.dbo.sp_send_dbmail
						@profile_name = @profile_name,
						@recipients=@recipients,
						@copy_recipients = @copy_recipients,
						@blind_copy_recipients = @blind_copy_recipients,
						@subject=@subject,
						@body=@body,
						@body_format=@body_format,
						@from_address=@from_address,
						@reply_to=@from_address,
						@file_attachments=@file_attachments

				IF ISNULL(@Test,0) = 0
				BEGIN
					UPDATE  msdb.dbo.sysmail_mailitems
					SET     sent_status = 3
					WHERE   mailitem_id = @mailitem_id
				END
            END

        CLOSE SYSMAIL_LOG_RESEND_CURSOR

        DEALLOCATE SYSMAIL_LOG_RESEND_CURSOR

    END

GO

Advertisements

3 thoughts on “Resend failed emails from Database Mail Stored Procedure

  1. Nice, thanks! I think you probably meant to remove “AND l.mailitem_id > 6814” before posting online, since that seems specific to your situation (and also lost the ‘>’ in translation). For reuse, I would suggest adding a quick minID and maxID param (to use in the select), or accepting a TVP of mailIDs so you could easily specify which mail items to resend.

    😉

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s