SQL Server如何重新发送“发送失败”的邮件？

在SQL Server中，如何重新发送那些曾经”发送失败“的邮件呢？有时候由于SMTP服务器故障或一些其他特殊因素，导致邮件发送失败。但是这些邮件又非常重要，应用程序由于设计的局限性或其他一些因素，无法重新发送这些邮件，事后需要我们从数据库的层面去重新发送这些“发送失败“的邮件，下面这个脚本正是解决这个问题的利器。如果你工作中偶尔也会遇到这类需求的话，那么这个脚本可以解决你的不时之需。

/**************************************************************************************************************
    --脚本名称  ：      resend_failed_emails.sql
    --脚本作者  :       潇湘隐者
    --创建日期  :       2021-06-29
***************************************************************************************************************
    脚本功能    ：       重新发送发送失败的邮件
***************************************************************************************************************
    注意事项    :       1:在执行脚本前,先查询需要重新发送那些发送失败的邮件，然后修改脚本中游标的查询条件
***************************************************************************************************************
    参考资料    :       无
***************************************************************************************************************
    更新记录    ：      2021-06-29 创建此脚本
         
***************************************************************************************************************/
DECLARE @mail_recipients VARCHAR(4000) 
       ,@mail_copy_recipients VARCHAR(4000) 
       ,@mail_subject VARCHAR(1000) 
       ,@mail_query_result_width INT 
       ,@mail_query_result_file INT
       ,@mail_body VARCHAR(8000) 
       ,@mail_query VARCHAR(4000) 
       ,@mailid INT 
       ,@mail_profile_name sysname
       ,@mail_body_format VARCHAR(100);

DECLARE @tv_fail_mailid TABLE (mailitem_id INT);

--此处游标，可以根据实际情况定制查询条件,筛选需要重新发送失败的邮件
DECLARE mail_cursor CURSOR READ_ONLY
FOR
    SELECT DISTINCT
            a.mailitem_id
    FROM    msdb..sysmail_allitems a
            LEFT JOIN msdb.dbo.sysmail_event_log b ( NOLOCK ) ON a.mailitem_id = b.mailitem_id
    WHERE   ( b.log_id IS NOT NULL )
            AND event_type <> 'success'
            AND a.sent_status = 'failed'
            --AND a.subject LIKE N'%xxx';
            --AND a.mailitem_id IN (180019,180021)

OPEN mail_cursor;
FETCH NEXT FROM mail_cursor 
INTO @mailid;
WHILE @@FETCH_STATUS = 0
    BEGIN

        SELECT  @mail_recipients = recipients ,
                @mail_copy_recipients = copy_recipients ,
                @mail_subject = subject ,
                @mail_body = body ,
                @mail_body_format = body_format ,
                @mail_query_result_file = attach_query_result_as_file ,
                @mail_query = query ,
                @mail_query_result_width = query_result_width,
                @mail_profile_name =(SELECT sp.name FROM msdb.dbo.sysmail_profile sp WHERE sp.profile_id = m.profile_id)
        FROM    msdb..sysmail_allitems m
        WHERE   mailitem_id = @mailid;

        INSERT INTO @tv_fail_mailid
                ( mailitem_id )
        SELECT @mailid;

        IF @mail_query_result_file = 0
            SET @mail_query = '';
        EXEC msdb.dbo.sp_send_dbmail 
            @profile_name = @mail_profile_name,
            @recipients = @mail_recipients,
            @copy_recipients = @mail_copy_recipients, 
            @subject = @mail_subject,
            @body = @mail_body, 
            @body_format = @mail_body_format, 
            @query = @mail_query,
            @attach_query_result_as_file = @mail_query_result_file,
            @query_result_width = @mail_query_result_width;
        
        FETCH NEXT FROM mail_cursor     INTO @mailid;
    END;
CLOSE mail_cursor;
DEALLOCATE mail_cursor;

--输出重新发送过的邮件详细信息
SELECT m.* FROM msdb.dbo.sysmail_allitems m WITH(NOLOCK)
INNER JOIN @tv_fail_mailid f ON m.mailitem_id =f.mailitem_id 
ORDER BY m.sent_date DESC;