如何诊断修复SQL Server存储过程sp_send_dbmail查询格式化语法错误

根因说明 #

你遇到的语法错误核心原因是@query变量的长度设置过小，你当前定义为VARCHAR(1000)，但你用到的复杂查询字符数已经远超1000，导致查询语句被截断，截断位置正好落在tblAPDTracker表名附近，所以才报对应语法错误；当你注释掉tblAPDTracker关联逻辑后，截断位置前移到Well表附近，就变成了Well表附近语法错误，和你观测到的现象完全吻合。

如何获取更详细的报错信息 #

• 调整sp_send_dbmail的参数@append_query_error = 1，这样查询执行错误的完整信息会直接附加在邮件内容中，不会只返回模糊的执行失败提示
• 执行存储过程前先通过PRINT @query或者SELECT @query输出拼接完成的完整查询语句，把输出的语句复制到SSMS中单独执行，就能直接定位截断、语法错误等问题
• 查询SQL Server内置的邮件错误日志，获取完整报错堆栈：

SELECT * FROM msdb.dbo.sysmail_event_log ORDER BY log_date DESC

修复方案 #

1. 把@query变量的长度调整为足够大的容量，直接改用VARCHAR(MAX)即可，完全能容纳你的查询内容：

-- 把原来的DECLARE @query VARCHAR(1000); 替换为
DECLARE @query VARCHAR(MAX);

2. 可选优化：把查询内写死的@SearchYear、@SearchMonth提取为存储过程的入参，后续调整统计时间无需修改存储过程内部逻辑
3. 修复后先打印完整@query确认没有截断，再执行存储过程测试，邮件就能正常输出你预期的CSV附件内容

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