如何用sp_send_dbmail发送JSON查询附件及解决sqlcmd初始化报错

1. 存储完整JSON字符串的NVARCHAR(MAX)列能否导出为JSON并邮件发送？ #

完全可以。该列本身已经是完整的JSON结构，只需通过sp_send_dbmail的@query参数直接查询该列，将结果作为JSON附件发送即可。关键是要避免查询输出的额外格式化破坏原有JSON，比如去掉表头、避免添加分隔符。

2. 修复sp_send_dbmail初始化sqlcmd库错误（错误码-2147417850） #

这个错误的核心是sqlcmd库初始化失败，以下是常见修复方案：

• 权限检查：
  • 执行sp_send_dbmail的账号必须属于msdb数据库的DatabaseMailUserRole角色
  • 该账号需要具备目标数据库DBName的db_datareader权限
  • 确认SQL Server代理服务账号有读取目标数据库及相关系统资源的权限
• 调整查询输出参数：
  默认的查询输出格式可能会破坏JSON结构，添加以下参数可以解决问题：
  • @query_result_separator = ''：消除默认分隔符对JSON的干扰
  • @query_no_truncate = 1：防止长JSON字符串被截断
  • @query_result_header = 0：移除查询结果表头，避免污染JSON内容
    修改后的代码示例：

  EXEC msdb.dbo.sp_send_dbmail  
    @profile_name = 'Profile',
    @recipients = 'a@b.com',
    @subject = @Subject,
    @body = @Body,  
    @query = 'SELECT TOP(1) [col] FROM [dbo].[tbl] ORDER BY [Date] DESC;',
    @execute_query_database = 'DBName',
    @attach_query_result_as_file = 1,
    @query_attachment_filename = 'Report.json',
    @query_result_separator = '',
    @query_no_truncate = 1,
    @query_result_header = 0;
  

• 检查数据库兼容性级别：
  如果目标数据库兼容性级别低于100（对应SQL Server 2008），可能导致sqlcmd无法正常处理查询。调整到合适的级别（根据你的SQL Server版本，比如2019对应150）：

  ALTER DATABASE DBName SET COMPATIBILITY_LEVEL = 150;
  

• 确认SQL Server代理状态：
  确保SQL Server代理服务处于运行状态，服务账号拥有足够的系统权限。

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