Tuesday, May 29, 2007

Resending sysmail emails

Oops, mail server went down you determine. Then you think to yourself, that is not good for my sysmail I use with SQL. You realize sysmail in SQL 2005 retries and you feel better. Oh, it gets worse sysmail gave up after so many tries. Users didn't receive the emails as expect. Below is a handy stored proc to send them out again using the information in the sysmail_log table. Warning: This stored procedure does NOT include all fields. It only include recipients, subject, body, and uses the original mail profile that was used to send the email. Things like attachments, bcc, and other items are not included, but could easily be added. The stored procedure below takes the MailItemID from the msdb.dbo.sysmail_mailItems table as a parameter. Calling this stored procedure will resend the email with the specified MailItemID. create proc ResendEmailItem(@MailItemID as int) as Declare @MyMailProfile as nvarchar(128) Declare @MyRecipients as varchar(4000) Declare @MySubject as nvarchar(255) Declare @MyBody as nvarchar(4000) select distinct @MyMailProfile = p.name, @MyRecipients = i.recipients, @MySubject = i.subject, @MyBody = i.body from msdb.dbo.sysmail_log l join msdb.dbo.sysmail_mailitems i on (l.mailitem_id = i.mailitem_id) join msdb.dbo.sysmail_profile p on (i.profile_id = p.profile_id) where l.mailitem_id = @MailItemID EXEC msdb.dbo.sp_send_dbmail @profile_name = @MyMailProfile, @recipients = @MyRecipients, @subject = @MySubject, @body = @MyBody Example Usage: ResendEmailItem 3698 -- 3698 is the MailItemID

13 comments:

Michael said...

Thanks for the useful article resending sysmail.

Do you have maybe also some example code, how to resend/re-feed an existing attachements thru sp_send_dbmail,
based on the table sysmail_attachments, which stores the mail related attachement als varbinary(max)?
I'm especially asking how to re-feed the varbinary data...

any help appreciated ;-)

michael

Brent V said...

michael,

I'm glad you found the article useful! I have not done anything with attachments in this regards. However, I did spend a few minutes looking into it. I don't see any straight forward way to do it. You could modify the stored procedure, but that is probably a maintenance nightmare. I would recommend reading the varbinary data into a physical file then attaching like you normally would when you call sp_send_dbmail. There are many ways to do this. You can use a CLR Stored Procedure in SQL 2005 to get a binary stream. You could also use an extended stored procedure if you are in SQL 2000 (or SQL 2005). You could use bcp if you want to call a command line utility to create the file instead of writing custom .Net code.

Another option would be to copy sp_send_dbmail and call it something like resend_dbmail and modify it so that it doesn't pull the attachment from file system, but instead pulls it from the sysmail_attachments table. The drawback being if they change the implementation of dbmail then your code may break. Then again, what is described in my article may also. :) I looked through the code and I think it is doable, but it looks like a lot of work to understand everything that is going on.

Here are some useful links I found:

Writing and Reading binary data using .Net (I assume this would work in a CLR stored procedure as well). I read that this article successfully worked when datatype is varbinary instead.
http://www.codeproject.com/KB/database/ImageSaveInDataBase.aspx

How to write a CLR stored procedure:
http://msdn2.microsoft.com/en-us/library/ms131094.aspx

Michael Brönnimann said...

brent,

thank you very much for showing the different alternate approaches and the helpful information and links.

my favorite is presumably as suggested by you to create a modified sp_send_dbmail code (resend_dbmail) as it relies on the the same system tables like your code sample, which means the code is invulnerable to changes in the same way ;-)

Brent V said...

Michael,

Best of luck to you. If you would be so kind, please post your solution if you can (assuming you get it to work). Or if you don't feel comfortable with that, please let me how you ended up getting it to work or not.

Brad said...

Here's the code I have in my SQL Server Agent Jobs that fires every hour...

CREATE PROCEDURE sysmail_resend_timeout
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
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.description LIKE '%The operation has timed out%'
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)
FETCH NEXT FROM SYSMAIL_LOG_RESEND_CURSOR INTO
@mailitem_id
, @profile_name
, @recipients
, @subject
, @body_format
, @body
IF NOT @@FETCH_STATUS = 0 BEGIN
BREAK
END

PRINT CONVERT(varchar, GETDATE(), 121) + CHAR(9) + CONVERT(varchar, @mailitem_id) + CHAR(9) + @recipients

EXEC msdb.dbo.sp_send_dbmail
@profile_name = @profile_name
, @recipients = @recipients
, @subject = @subject
, @body_format = @body_format
, @body = @body

UPDATE msdb.dbo.sysmail_mailitems
SET
sent_status = 3
WHERE
mailitem_id = @mailitem_id

END

CLOSE SYSMAIL_LOG_RESEND_CURSOR

DEALLOCATE SYSMAIL_LOG_RESEND_CURSOR

END
GO

Brent V said...

Hi Michael,

Thank you for sharing this great code. I have not tried it yet, but it looks quite useful. Awesome addition. Thank you!

Brent

Sameer said...
This comment has been removed by the author.
Sameer said...

Thanks for all of you for sharing the articles.
I need to get the email addresse's when we send email to multiple email id's and email fails for only few of them.Is there any way where i can find out the only failed email id's.

Thanks in advance !!!!

Brent V said...

Sameer,

I would start with the query that Brad (see comments) uses. I have not tried the query, but it sounds like it will do what you need. Look at the first select statement there.

I hope that helps. Thank you for your comments.

Brent

Grady said...

Thanks. This solved a big problem for me. Grady Christie

Brent V said...

Grady,

Glad to help. Thanks for the feedback!

Brent

IngeX said...

Thanks Brent & Brad your codes save my life

Anonymous said...

Great code. Thanks.