Tuesday 15 December 2009

SQL 2008 Database Mail Resend

Try this query to resend failed items in the Database Mail queue.

declare @mailItem int
declare
@mailRequest nvarchar(max)
declare @mailRecipient nvarchar(max)
declare @mailSubject nvarchar(255)
declare @mailSendDate datetime
declare
@statusMsg nvarchar(400)
declare @rc int

declare
curFailedMail cursor fast_forward for
SELECT
[mailitem_id]
FROM [msdb].[dbo].[sysmail_faileditems]

open curFailedMail
fetch next from curFailedMail into @mailItem

while @@fetch_status = 0
begin -- fetch loop
-- Create request xml
SET @mailRequest = '<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) + N'</MailItemId></requests:SendMail>'

-- put the request on the queue.
EXEC @rc = msdb..sp_SendMailQueues @mailRequest
IF @rc = 0
BEGIN -- resend success
set @statusMsg = N'Mailitem "' + convert(nvarchar(20), @mailItem) + '" '
+ N', was added to the queue for re-sending.'
RAISERROR(@statusMsg, 10, 1) WITH NOWAIT
END
-- resend success
ELSE
BEGIN
-- resend failure
RAISERROR(14627, 16, 1, @rc, 'send mail') WITH LOG
END -- resend failure

fetch next from curFailedMail into @mailItem

end -- fetch loop

close curFailedMail
deallocate curFailedMail

7 comments:

The Faziltons said...

This script worked perfectly - thanks a lot!

Anonymous said...

Thanks a lot. It worked perfectly. Also, Here I include a query to check whether the mails are sent.

select * from msdb..sysmail_faileditems where mailitem_id in
( select mailitem_id from msdb.dbo.sysmail_sentitems )

We can delete these items from failed list

Thanks again

Liam said...

Ditto, thanks Phil

Anonymous said...

Nice script. Worked really well. Depending on how you use Database mail i would caution you on just running it without checking to see what has failed in the past. I had customer alert emails that were over a year old that would have been sent out with this script. Its a simple solution, just add a WHERE clause to the cursor to select >= a specific mailitem_id. I'm going to hang on to this script because i am sure i will use it again.

Paul said...

Brilliant script! Many thanks Phil

Anonymous said...

Perfect, it´s exactly that solution I needed

Thank's a lot!

Anonymous said...

2015 checking in... yup, still works. Thanks!