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

Use the result set of a stored procedure

To pipe the result set of a stored procedure just use exec command in an INSERT statement…

CREATE TABLE #Table1 (

ID int
,[Value] varchar(250)

)
GO
INSERT INTO #Table1 VALUES(1, 'one')
INSERT INTO #Table1 VALUES(2, 'two')
INSERT INTO #Table1 VALUES(3, 'three')
INSERT INTO #Table1 VALUES(4, 'four')
INSERT INTO #Table1 VALUES(5, 'five')
INSERT INTO #Table1 VALUES(6, 'six')
GO
CREATE PROCEDURE spTestInsert
AS
BEGIN
SELECT * FROM #Table1
END
GO
CREATE TABLE #Table2 (

ID int
,[Value] varchar(250)

)
GO
insert #Table2 exec spTestInsert

select * from #Table2

DROP TABLE #Table1
DROP TABLE #Table2
DROP PROCEDURE spTestInsert

SQL Server Stored Procedure to generate temp table with dynamic columns

At work we required some MI which produced a monthly analysis of certain figures, the recordset I was after would  have whatever we are measuring in the first column, followed by each month of the current year. In my shameful past I would have probaly managed this in my server side code, but I knew this could all be done in SQL and return a nice recordset which only had to be iterated through.  This was the stored procedure:


PROCEDURE [dbo].[spPerilByMonthMI]
--Insurers in form (1,2,3,4,5)

@Insurers nVarchar(1000)
AS

--MONTH
DECLARE @CurrentMonth Integer
SELECT @CurrentMonth = MONTH(getDate())
--YEAR
DECLARE @CurrentYear Integer
SELECT @CurrentYear = YEAR(getDate())

DECLARE @i Integer
DECLARE @SQL nVarchar(1000)
DECLARE @NumberOfPeril Integer
DECLARE @ParamDefinition nVarchar(500)
SET @ParamDefinition = N'@NumberOfPeril Integer OUTPUT';
DECLARE @ValuesSQL nVarchar(100)
DECLARE @ColName nVarchar(100)
DECLARE @SumRowSQL nVarchar(1000)
SET @SumRowSQL =''

CREATE TABLE #MI (
[Peril] varchar(40))

-- Add columns to table
SET @i=1
WHILE (@i1)
BEGIN
SET @SumRowSQL = @SumRowSQL + '+'
END

SET @SumRowSql = @SumRowSql + '['+@ColName+']'

SET @i=@i+1
END

ALTER TABLE #MI ADD [Total] integer NULL

DECLARE @PerilID Integer
DECLARE @Description nvarchar(40)

DECLARE cur CURSOR FOR
SELECT PerilID, [Description] FROM tblPerils
OPEN cur

FETCH NEXT FROM cur INTO @PerilID,@Description

WHILE @@FETCH_STATUS = 0
BEGIN
SET @ValuesSQL = ''
SET @i=1
WHILE (@i1)
BEGIN
SET @ValuesSQL = @ValuesSQL +','
END

SET @ValuesSQL = @ValuesSQL + CAST(@NumberOfPeril As varchar(10))

SET @i=@i+1
END

SET @SQL = 'INSERT INTO #MI VALUES('''+@Description+''','+ @ValuesSQL+',0)'
PRINT(@SQL)
--EXEC(@SQL)
--Total Row

SET @SQL = N'UPDATE #MI SET Total = (SELECT ('+ @SumRowSQL +') FROM #MI WHERE Peril = '''+ @Description +''') WHERE Peril = '''+ @Description +''''

--EXEC(@SQL)
FETCH NEXT FROM cur INTO @PerilID,@Description
END

CLOSE cur
DEALLOCATE cur

SELECT * FROM #MI

DROP TABLE #MI
GO

As can be seen, a loop is set up until it gets to the current month, each iteration generates an add statement to add a column to the temp table. The next loop is going through the table where we are getting the counts to populate each month. This uses the sp_executesql stored procedure to populate the @NumberOfPeril value, which in turn is added to the @Values parameter, which builds up the insert string.