September 21, 2015 at 4:39 am
Dear All,
I have some code to resend failed mail items, as:
-- to resend a fail
DECLARE @to varchar(max)
DECLARE @copy varchar(max)
DECLARE @title nvarchar(255)
DECLARE @msg nvarchar(max)
SELECT @to = recipients, @copy = copy_recipients, @title = [subject], @msg = body
FROM msdb.dbo.sysmail_faileditems
WHERE mailitem_id = <failmailid>
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'obmail',
@recipients = @to,
@copy_recipients = @copy,
@body = @msg,
@subject = @title,
@body_format = 'HTML';
However, I see that attachments are stored as varbinary in sysmail_mailattachments. I'd like to reconstitute these at resend time, so as to recreate the entire mail that's failed, not just the HTML part. I'm thinking I'd need to do something like BCP out the file, using the filename noted in table, and then add this in to the sp_send_dbmail construct. But this seems inelegant, so would appreciate some other ideas.
September 21, 2015 at 5:52 am
i have this saved in my snippets, I've only used it a handful of times.
rather than rebuilding the emails, it's just re-queueing the email as however it was originally built:
declare @mailitem_id int,
@sendmailxml varchar(max),
@rc int,
@StartDate datetime,
@EndDate datetime
-- get the dates to use in the query
set @StartDate = convert(datetime, convert(char(8), GetDate(), 112))
set @EndDate = @StartDate + 1
declare cFailedMail cursor for
SELECT mailitem_id
FROM [msdb].[dbo].[sysmail_faileditems]
WHERE send_request_date >= @StartDate
AND send_request_date < @EndDate
open cFailedMail
fetch next from cFailedMail into @mailitem_id
while @@fetch_status = 0 begin
-- Create the primary SSB xml maessage
SET @sendmailxml = '<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_id) + N'</MailItemId></requests:SendMail>'
-- Send the send request on queue.
EXEC @rc = sp_SendMailQueues @sendmailxml
IF @rc <> 0
BEGIN
RAISERROR(14627, 16, 1, @rc, 'send mail')
END
fetch next from cFailedMail into @mailitem_id
end
close cFailedMail
deallocate cFailedMail
Lowell
September 21, 2015 at 6:08 am
Thanks Lowell, that's a useful snippet and works great.
Regards, Greg.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply