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;
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;
@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;
