Counter of Festivals

Ashok Blog for SQL Learners and Beginners and Experts

Wednesday, 20 May 2015

Check Failed Emails and ReSend Emails Script using DataBaseMail in SQL Server

Check Failed Emails and ReSend Emails  Script using DataBaseMail in SQL Server:

Failed Email Details are saved in below bolded Table in SQL Server

SELECT  mailitem_id,recipients,subject,body,send_request_date FROM msdb.dbo.sysmail_faileditems
where convert(varchar(10),send_request_date,121) Between convert(varchar(10),getdate()-2,121) and convert(varchar(10),getdate(),121)

order by send_request_date desc

So we can see below Script to find and resend Failed Emails using dbmail.

Script:

SET NOCOUNT ON;
GO

DECLARE @EmailFailedTable TABLE(id INT IDENTITY,mailitem_id int,recipients varchar(max),subject varchar(max),body varchar(max))

INSERT INTO @EmailFailedTable (mailitem_id,recipients,subject,body)

SELECT  mailitem_id,recipients,subject,body FROM msdb.dbo.sysmail_faileditems
where convert(varchar,send_request_date,121) between convert(varchar,getdate()-7,121) and convert(varchar,getdate(),121)


DECLARE @Rec VARCHAR(MAX),@Sub varchar(MAX),@body varchar(max)

DECLARE curFailedEmail CURSOR DYNAMIC FOR

SELECT recipients,subject,body  FROM @EmailFailedTable

OPEN curFailedEmail

BEGIN

FETCH NEXT FROM curFailedEmail INTO @Rec,@Sub,@body

WHILE @@FETCH_STATUS = 0
   
BEGIN

PRINT 'Rec : '+ convert(varchar(100),@Rec)+', Sub : '+ convert(varchar(max),@Sub)+ ', body : '+convert(varchar(max),@body)

 EXEC msdb.dbo.sp_send_dbmail
@Profile_name = 'Intimate',
@recipients = @Rec,
@body = @body,
@subject = @Sub

FETCH NEXT FROM curFailedEmail INTO @Sub,@body,@Rec
    END
END
CLOSE curFailedEmail

DEALLOCATE curFailedEmail