Sometimes emails from SQL Server go missing, especially when you share an inbox with colleagues. On most occasions it doesn’t always matter as the job that generated the email can simply be re ran to produce the email once again, but what about those emails that contain time specific information? we cannot simple just kick off the job again as it may be coded to evaluate right now and not provide us with the information that was sent 2 hours ago.
I was faced with exactly this issue the other day – i needed the information from the email that was sent at that given time so I wrote a bit of code to get the job done then decided that if I tidy this code up I could make it reusable so here is what I come up with:
Find the email you want to resend using one of the following views in msdb:
sysmail_allitems
sysmail_faileditems
Within these views there are a lot of columns at your disposal to narrow down your search such as:
recipients
recipients copy_recipients
blind_copy_recipients
subject
body
body_format
importance
sent_date
Once you have found the email you want to resend make a note of the mailitem_id and use the following code to resend the email:
--Set mail item id you want to re send
DECLARE @mailitemID INT = -- MailItem_Id here e.g 2041
DECLARE @profilename NVARCHAR(128);
DECLARE @recipients VARCHAR(MAX);
DECLARE @copy_recipients VARCHAR(MAX);
DECLARE @blind_copy_recipients VARCHAR(MAX);
DECLARE @subject NVARCHAR(510);
DECLARE @body VARCHAR(MAX);
DECLARE @body_format VARCHAR(20);
DECLARE @importance VARCHAR(6);
DECLARE @sensitivity VARCHAR(12);
DECLARE @query BIT;
DECLARE @attachment BIT;
--Get the information for the email
SELECT
@profilename = [profiles].[name],
@recipients = [mail].[recipients],
@copy_recipients = [mail].[copy_recipients],
@blind_copy_recipients = [mail].[blind_copy_recipients],
@subject = [mail].[subject],
@body = [mail].[body],
@body_format = [mail].[body_format],
@importance = [mail].[importance] ,
@sensitivity = [mail].[sensitivity],
@query = CASE WHEN [mail].[query] IS NULL THEN 0 ELSE 1 END,
@attachment = CASE WHEN [mail].[file_attachments] IS NULL THEN 0 ELSE 1 END
FROM msdb.dbo.sysmail_allitems mail
INNER JOIN msdb.dbo.sysmail_profile profiles ON mail.profile_id = profiles.profile_id
WHERE mailitem_id = @mailitemID;
IF @query = 0 AND @attachment = 0
BEGIN
--Re send the email
EXEC msdb.dbo.sp_send_dbmail
@profile_name = @profilename,
@recipients = @recipients,
@copy_recipients = @copy_recipients,
@blind_copy_recipients = @blind_copy_recipients,
@subject = @subject,
@body = @body,
@body_format = @body_format,
@importance = @importance,
@sensitivity = @sensitivity
END
ELSE
BEGIN
RAISERROR('The mailitem_id specified (%d) uses a query or attachments, unfortunately this script cannot send your email',11,0,@mailitemID)
END
This will send the email exactly how it was sent originally, there are a couple of gotchas – This script will not handle attachments , i am sure that it is possible but I had no need for attachments but feel free to code that in yourself if you need it 🙂
Thanks for reading.
SQL Undercover 
Hi
While your script may be very useful, this will create a new mail in the queue. Following a script to actually retry sending an email (it works with attachments as long as the respective file is still available)
By default the script only shows mail items in error after a specified date, setting an actual Mail Item ID will resend that specific mail item, and it also resends all failed emails in the last x hours, when a negative integer in @MassSendLastHour. A delay between each item has been put in place since this helps with reducing “mass” sending errors.
It also checks for the stats of the service and starts if necessary, and allows to restart the mail service.
SET QUOTED_IDENTIFIER ON
declare @mailitem_id int = NULL,–Set to the mailitem to resend (this is asyncronous and takes a few seconds)
@EarliestDateforPersistedErrorListing datetime = ‘2018-05-31’,
@PersistedErrorCount INTEGER = 100,
@ErrorLogCount INTEGER = 10,
@MassSendLastHour INTEGER = 1,–Negative number sends failed items in the last X hours
@MassSendDelaysecs integer=10 –Mass send has a delay before sending each failed item
use msdb
/*
execute sysmail_stop_sp
execute sysmail_start_sp
*/
Set Nocount On
If Object_Id(‘tempdb..#Status’) IS NOT NULL DROP TABLE #Status
Create Table #Status (
[Status] Nvarchar(100)
)
Insert #Status
Exec msdb.dbo.sysmail_help_status_sp
If Not Exists (
Select Top 1
0
From #Status
Where Status = ‘STARTED’
)
Begin
Raiserror (‘Database Mail was not running, attempting to restart’,0, 1) With Nowait
Exec msdb.dbo.sysmail_start_sp
End
select * from #Status
select top (@PersistedErrorCount) send_request_date,lastQueued=sent_date, * from sysmail_faileditems where send_request_date>=@EarliestDateforPersistedErrorListing order by 1 desc
select top(@ErrorLogCount) ev.log_date, ev.description,ev.mailitem_id,f.send_request_date,f.subject,f.recipients,f.sent_date
from dbo.sysmail_event_log ev left join sysmail_allitems f on f.mailitem_id = ev.mailitem_id
where subject not like ‘Report Name exceeded%’
order by 1 desc
if @mailitem_id > 0
BEGIN
DECLARE @sendmailxml varchar(max),
@rc int
SET @sendmailxml = ”
+ CONVERT(NVARCHAR(20), @mailitem_id) + N”
— Send the send request on queue.
EXEC @rc = sp_SendMailQueues @sendmailxml
IF @rc 0 RAISERROR(14627, 16, 1, @rc, ‘send mail’)
END
DECLARE @MailItemsXML XML
SET @MailItemsXML = (
SELECT ‘@ID’ = mailitem_id,
‘@Subject’ = subject,
‘@RequestDate’ = send_request_date,
‘@LastTryDate’ = sent_date
FROM sysmail_faileditems
WHERE subject NOT LIKE ‘Report Name exceeded%’
AND send_request_date >= DATEADD(HOUR,@MassSendLastHour,GETDATE())
FOR XML PATH(‘MailItem’)–,ROOT(‘table’)
)
IF @MailItemsXML IS NOT NULL
BEGIN
SELECT @MailItemsXML
DECLARE @Statement NVARCHAR(MAX)
SET @Statement = ‘DECLARE @SendMailXML VARCHAR(MAX),
@rc INTEGER
‘ + (SELECT ‘WAITFOR DELAY ”00:00:’ + LTRIM(str(@MassSendDelaysecs)) +”’;RAISERROR(”’+ a.value(‘.’,’NVARCHAR(30)’) + ”’, 0, 1) WITH NOWAIT
EXEC @rc = sp_SendMailQueues N”’ + a.value(‘.’,’NVARCHAR(30)’)
+ N”’
IF @rc 0 RAISERROR(14627, 0, 1, @rc, ”send mail ID ‘+ a.value(‘.’,’NVARCHAR(30)’) + ”’)
‘
FROM @MailItemsXML.nodes(‘MailItem/@ID’) a(a)
FOR XML PATH(”),TYPE
).value(‘.’,’NVARCHAR(MAX)’)
EXEC sp_executesql @Statement
END
LikeLike
I can see what you’re doing but wondering why you’ve decided to use undocumented system procs rather than the standard sp_send_dbmail that Adrian’s using here?
I’d personally be wary of running anything that includes undocumented procs at a customer site.
LikeLike