Letzte Nachte gab es ein Netzwerkproblem. Wir haben festgestellt, dass mehrere E-Mail aus dem SQL Server nicht versendet wurden.

select * from msdb.dbo.sysmail_faileditems
order by send_request_date desc

Nun möchten wir die nicht versendeten E-Mails trotzdem noch versenden. Mit nachfolgendem Skript lässt sich das erledigen. Der Zeitraum ist im Kopf des Skriptes anzupassen.

set xact_abort on

begin tran

DECLARE @send_after_date datetime = '20180626 00:00'
DECLARE @send_before_date datetime = '20180627 11:00'

DECLARE @recipients        varchar(max)
DECLARE @copy_recipients    varchar(max)
DECLARE @subject    nvarchar(510)
DECLARE @body    nvarchar(max)
DECLARE @body_format    varchar(20)
DECLARE @query    nvarchar(max)
DECLARE @execute_query_database sysname

declare c cursor for
select recipients, copy_recipients, subject, body, body_format, query, execute_query_database
from sysmail_faileditems
where send_request_date > @send_after_date
and send_request_date < @send_before_date
order by send_request_date

OPEN c
FETCH NEXT FROM c INTO @recipients, @copy_recipients, @subject, @body, @body_format, @query, @execute_query_database

WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @recipients, @copy_recipients, @subject, @body, @body_format, @query, @execute_query_database

EXEC msdb.dbo.sp_send_dbmail
@recipients = @recipients,
@copy_recipients = @copy_recipients,
@body = @body,
@subject = @subject,
@body_format = @body_format,
@query = @query,
@execute_query_database = @execute_query_database;

FETCH NEXT FROM c INTO @recipients, @copy_recipients, @subject, @body, @body_format, @query, @execute_query_database
END

CLOSE c
DEALLOCATE c

commit

Viel Spaß damit!