Resend an email sent from SQL Server using TSQL

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

2018-06-05 19_52_48

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.

3 thoughts on “Resend an email sent from SQL Server using TSQL

Add yours

  1. 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

    Like

    1. 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.

      Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

w

Connecting to %s

Create a website or blog at WordPress.com

Up ↑

%d bloggers like this: