SQL Server调用sp_send_dbmail发送CSV邮件附件格式错乱如何解决

问题根因 #

sp_send_dbmail默认生成查询结果时的两个机制会导致CSV格式错乱：

1. 默认开启字段空格填充，为了让命令行输出对齐，会给每个字段补充大量占位空格，破坏分隔符识别逻辑
2. 未对字段内的特殊字符（逗号、双引号、换行符）做转义，SAP B1的memo这类文本字段普遍存在这类字符，会被识别为字段分隔符，导致单字段拆分为多列。

最优解决方案 #

调整存储过程参数+改写查询语句做字段转义，优化后的代码如下：

EXEC msdb.dbo.sp_send_dbmail 
          @profile_name = 'profile name',
          @recipients = 'recipent name',
          @subject = 'Test', 
          @query = 'SELECT 
                ''"'' + REPLACE(CAST(slpcode AS VARCHAR(255)), ''"'', ''""'') + ''"'',
                ''"'' + REPLACE(CAST(slpname AS VARCHAR(255)), ''"'', ''""'') + ''"'',
                ''"'' + REPLACE(CAST(memo AS VARCHAR(MAX)), ''"'', ''""'') + ''"'',
                ''"'' + REPLACE(CAST(commission AS VARCHAR(255)), ''"'', ''""'') + ''"''
          FROM dbo.OSLP ',
          @attach_query_result_as_file = 1,
          @query_attachment_filename = 'Results.csv',
          @query_result_separator = ',', 
          @query_result_width = 32767,
          @query_result_header = 1,
          @append_query_error = 1,
          @query_no_truncate = 1,
          @query_result_no_padding = 1

调整说明 #

• 新增@query_result_no_padding = 1参数，关闭字段空格填充，消除占位空格导致的列错位问题
• 将@query_no_truncate改为1，避免长文本字段被截断导致后续内容偏移
• 查询语句中给所有字段包裹双引号，同时把字段内原本存在的双引号替换为两个双引号做转义，完全符合CSV标准格式，即使字段内容包含逗号、换行符也会被识别为单个完整字段
• 所有字段做显式类型转换，避免不同数据类型拼接时报错

可选优化（针对Excel打开乱码的场景） #

如果业务用户用国内版Excel打开CSV出现中文乱码，可以在查询语句最开头拼接一行SELECT ''SEP=,'' UNION ALL，指定CSV分隔符，同时通知用户右键CSV文件用记事本打开后另存为，编码选择ANSI即可正常识别。

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