How to resend a message using dbmail

I'm a good DBA, developer, and all around wonderful human being. That means I love sets, hate cursors, and document my databases in my spare time.

However.

In this life, we all find strange things that can only be done one way. The below script is, I think, one of those unfortunate examples of that, in that it uses a cursor to get work done. However, if you can find a better way, email it or post a comment, and let's have a discussion.

Here's the short version:

I use SQL Agent to run a bunch of jobs overnight. SQL Agent is configured to send me the job status at the end of the job, succeed or fail.

In my situation, I needed to get all the failed messages for some time period to audit them. In order to get SQL Server dbmail to re-send the messages, I couldn't find an easy way to do this, so I made my own way to force the messages that weren't sent to get resent.

This is one of those cases where cursors make sense, because essentially, this is dynamic sql, due to the requirement to call the SP with the parameter values that change with every iteration of the loop.

I don't like being forced to use a cursor, but it works, it did indeed resend the emails, and it let me audit what I needed.

USE msdb;
go

DECLARE c1 CURSOR FOR
  SELECT DISTINCT
  f.recipients
  ,
  f.subject,
  f.body,
  f.importance,
  f.sensitivity
  FROM   msdb.dbo.sysmail_faileditems AS f
         JOIN msdb.dbo.sysmail_sentitems AS s
           ON f.profile_id = s.profile_id
              AND Isnull(f.recipients, '') = Isnull(s.recipients, '')
              AND Isnull(f.copy_recipients, '') = Isnull(s.copy_recipients, '')
              AND Isnull(f.blind_copy_recipients, '') =
                  Isnull(s.blind_copy_recipients, '')
              AND f.importance = s.importance
              AND f.sensitivity = s.sensitivity
  WHERE  Datediff(dd, f.sent_date, Getdate()) < 31
         AND f.body <> s.body;

DECLARE @Failed_profile_name                SYSNAME,
        @Failed_recipients                  VARCHAR(max),
        @Failed_copy_recipients             VARCHAR(max),
        @Failed_blind_copy_recipients       VARCHAR(max),
        @Failed_from_address                VARCHAR(max),
        @Failed_reply_to                    VARCHAR(max),
        @Failed_subject                     NVARCHAR(255),
        @Failed_body                        NVARCHAR(max),
        @Failed_body_format                 VARCHAR(20),
        @Failed_importance                  VARCHAR(6),
        @Failed_sensitivity                 VARCHAR(12),
        @Failed_file_attachments            NVARCHAR(max),
        @Failed_query                       NVARCHAR(max),
        @Failed_execute_query_database      SYSNAME,
        @Failed_attach_query_result_as_file BIT,
        @Failed_query_attachment_filename   NVARCHAR(255),
        @Failed_query_result_header         BIT,
        @Failed_query_result_width          INT,
        @Failed_query_result_separator      CHAR(1),
        @Failed_exclude_query_output        BIT,
        @Failed_append_query_error          BIT,
        @Failed_query_no_truncate           BIT,
        @Failed_query_result_no_padding     BIT
;

OPEN c1;

FETCH next FROM c1 INTO @Failed_recipients, @Failed_subject, @Failed_body,
@Failed_importance, @Failed_sensitivity;

WHILE @@fetch_status = 0
  BEGIN
      FETCH next FROM c1 INTO @Failed_recipients, @Failed_subject, @Failed_body,
      @Failed_importance, @Failed_sensitivity;

      EXEC Sp_send_dbmail
        @profile_name = 'Default Profile',
        @recipients = @Failed_recipients,
        @from_address = 'era@HQ.DHS.GOV',
        @reply_to = 'era@HQ.DHS.GOV',
        @subject = @Failed_subject,
        @body = @Failed_body,
        @importance = @Failed_importance,
        @sensitivity = @Failed_sensitivity;
  END

CLOSE c1;

DEALLOCATE c1;