如何通过SQL Server代理调度将查询结果以JSON格式发送至指定邮箱

步骤1：将SQL查询结果转为JSON格式 #

用SQL Server自带的FOR JSON语法直接生成JSON结果，两种常用写法：

• FOR JSON AUTO：根据表结构自动生成JSON层级
• FOR JSON PATH：自定义JSON字段名和结构

示例代码：

-- 生成带顶层节点的JSON
DECLARE @jsonResult NVARCHAR(MAX)
SET @jsonResult = (
    SELECT Id, Name, CreateTime
    FROM YourTargetTable
    WHERE CreateTime >= DATEADD(DAY, -1, GETDATE()) -- 示例筛选条件
    FOR JSON AUTO, ROOT('daily_data')
)

步骤2：通过Database Mail发送带JSON的邮件 #

首先确认SQL Server的Database Mail已配置完成（在Management Studio的「管理」->「Database Mail」中创建邮件账户和配置文件），然后用系统存储过程发送邮件：

EXEC msdb.dbo.sp_send_dbmail
    @profile_name = '你的邮件配置文件名', -- 替换为实际配置文件名
    @recipients = 'target_email@domain.com', -- 目标邮箱
    @subject = '每日SQL查询结果JSON',
    @body = @jsonResult,
    @body_format = 'TEXT' -- JSON内容用TEXT格式即可

如果需要把JSON作为附件发送，可先将JSON写入服务器本地文件（需确保SQL Server账户有写入权限），再通过@file_attachments参数指定附件路径。

步骤3：用SQL Server代理创建定时作业 #

1. 打开Management Studio，展开「SQL Server代理」，右键「作业」->「新建作业」
2. 填写作业名称（如「每日发送JSON查询结果」），选择合适的所有者
3. 切换到「步骤」选项卡，点击「新建」：
   • 步骤名称：比如「生成JSON并发送邮件」
   • 类型：选择「Transact-SQL脚本(T-SQL)」
   • 数据库：指定查询所在的目标数据库
   • 命令框粘贴前面的完整T-SQL脚本（声明变量+生成JSON+发送邮件）
   • 点击「确定」保存步骤
4. 切换到「计划」选项卡，点击「新建」：
   • 设置计划名称，选择调度类型（如「重复执行」）
   • 配置执行频率（每日/每周/每月）、具体执行时间，完成后保存计划
5. 保存整个作业，右键作业选择「启动作业」测试运行是否正常

关键注意事项 #

• 确保SQL Server Agent服务处于运行状态（可在Windows服务列表中查看）
• 作业执行账户需要拥有：查询目标表的权限、调用sp_send_dbmail的权限、SQL Server代理作业的执行权限
• 若JSON结果过大，注意NVARCHAR(MAX)的容量限制，可考虑分批次处理或压缩内容
• 测试时先手动执行T-SQL脚本，确认JSON生成和邮件发送正常后，再配置定时调度

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