SQL Server 2016定时删数后发送含表名与删除记录数的邮件报告

核心思路 #

先把删除记录数和目标表名存入变量，再通过调整sp_send_dbmail的参数优化附件格式，或者用HTML格式展示邮件正文，彻底解决表格显示异常的问题。

具体实现步骤 #

1. 存储删除记录数与表名 #

先执行删除操作，将记录数和表名存入变量，避免@@ROWCOUNT被后续操作覆盖：

DECLARE @DeletedCount INT
DECLARE @TableName NVARCHAR(128) = N'dbo.f03b11z1'

-- 执行删除逻辑
DELETE FROM dbo.f03b11z1
WHERE vjupmj < CONCAT(DATEDIFF(year,'1900-1-1',(GETDATE()-220)) /100,DATEDIFF(year,'1900-1-1',(GETDATE()-220)) % 100, DATEPART(DAYOFYEAR,(GETDATE()-220)) )
  AND vjedsp = '1'

SET @DeletedCount = @@ROWCOUNT

2. 优化附件格式的邮件发送方案 #

用CSV格式作为附件（比默认文本格式更规整），同时调整分隔符、宽度等参数：

EXEC msdb.dbo.sp_send_dbmail
    @profile_name = '你的邮件配置文件名', -- 替换为实际的数据库邮件配置文件
    @recipients = '收件人邮箱地址', -- 替换为实际收件人邮箱
    @subject = '定时删除任务执行报告',
    @body = '本次定时删除任务执行结果如下，详细数据见附件。',
    @query = N'SELECT ''' + @TableName + N''' AS 目标表名, ' + CAST(@DeletedCount AS NVARCHAR(10)) + N' AS 删除记录数',
    @attach_query_result_as_file = 1,
    @query_result_filename = 'Delete_Report.csv',
    @query_result_separator = ',', -- CSV用逗号分隔，避免格式混乱
    @query_result_width = 200, -- 足够宽度防止内容换行错位
    @query_result_header = 1, -- 保留表头
    @exclude_query_output = 1, -- 不在正文重复显示查询结果
    @append_query_error = 1 -- 若查询出错，将错误信息附加到邮件中

3. 带HTML格式正文的邮件方案（可选） #

如果需要邮件正文直接展示规整的表格，用HTML格式构建正文，同时保留附件：

-- 构建HTML格式的正文内容
DECLARE @HtmlBody NVARCHAR(MAX)
SET @HtmlBody = N'<html><body>
<h3>定时删除任务执行报告</h3>
<table border="1" cellpadding="4">
    <tr><th>目标表名</th><th>删除记录数</th></tr>
    <tr><td>' + @TableName + N'</td><td>' + CAST(@DeletedCount AS NVARCHAR(10)) + N'</td></tr>
</table>
</body></html>'

-- 发送邮件
EXEC msdb.dbo.sp_send_dbmail
    @profile_name = '你的邮件配置文件名',
    @recipients = '收件人邮箱地址',
    @subject = '定时删除任务执行报告',
    @body = @HtmlBody,
    @body_format = 'HTML', -- 指定正文为HTML格式，表格自动规整
    @query = N'SELECT ''' + @TableName + N''' AS 目标表名, ' + CAST(@DeletedCount AS NVARCHAR(10)) + N' AS 删除记录数',
    @attach_query_result_as_file = 1,
    @query_result_filename = 'Delete_Report.txt',
    @query_result_separator = '|', -- 用竖线分隔避免文本对齐混乱
    @query_result_width = 100,
    @query_result_header = 1

格式异常原因说明 #

之前直接用SELECT 'dbo.f03b11z1' as TableName, @@ROWCOUNT AS 'Record_Count'时，默认的文本附件分隔符和列宽设置不合理，导致表格内容错位。用变量存储后构建查询，再通过指定附件格式（如CSV）、分隔符、宽度参数，就能彻底解决显示异常问题。

内容的提问来源于stack exchange，提问作者Michael Daniels