使用msdb生成HTML邮件时Outlook崩溃，行重复问题排查

使用msdb的sp_send_dbmail功能发送自动邮件时，将SQL查询结果转换为HTML表格后，邮件中的表格数据行不断重复，导致Outlook崩溃。但单独执行查询时仅返回一行数据，当前代码实现如下：

DECLARE @tableHTML NVARCHAR(max) = '';

SET @tableHTML =
'<h1>Picking en Rework</h1>' +
'<table border="1">' +
'<tr>' +
'<th>Order ETA</th>' +
'<th>To be Picked</th>' +
'<th>Rework Actions</th>' +
'<th>Destination Plts req order-line</th>' +
'<th>Destination Plts Req Total</th>' +
'<th>Reworked Plts On Stock</th>' +
'<th>Plts Left To Rework Order-Line</th>' +
'<th>Plts Left To Rework total</th>' +
'</tr>' +    
CAST
(   
( 
SELECT
                            
td = coalesce(vOuttakeOrderHeaders_2057.[ETA], ''),  '', 
td = coalesce([To be Picked],  ''), '',             
td = coalesce(vReworkReq_2057.[Rework Actions],  ''),  '',
td = coalesce(vReworkReq_2057.[Destination Plts Req Order-Line],  
''),'',
td = coalesce(vReworkReq_2057.[Destination Plts Req total],  ''),  '',
td = coalesce(vReworkReq_2057.[Reworked Plts On Stock],  ''),  '',
td = coalesce(vReworkReq_2057.[Plts Left To Rework Order-Line], ''),  
'',
td = coalesce(vReworkReq_2057.[Plts Left To Rework total],  ''),  ''
                                                                 
FROM [dbo].[vMailReworkPicking_2057], vOuttakeOrderDetails_2057
left join vOuttakeOrderHeaders_2057 on 
vOuttakeOrderDetails_2057.iOrderHeaderSysID = 
vOuttakeOrderHeaders_2057.iOrderHeaderSysID

left join vReworkReq_2057 on vOuttakeOrderDetails_2057.Article = 
vReworkReq_2057.Article
left join vMailReworkPicking_2057 on vOuttakeOrderHeaders_2057.[ETA] = 
vMailReworkPicking_2057.[Order ETA]

Where 
vOuttakeOrderHeaders_2057.[Order Type] like 'outtake'
and DisposedQuantity < TargetQuantity 
and RestPick > 0

ORDER BY vOuttakeOrderHeaders_2057.[ETA] DESC
FOR XML PATH('tr'), type) as nvarchar(max)) +
'</table>';

EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'test123',
@recipients = 'email',
@subject = 'Picking & Rework',
@body = @tableHTML,
@body_format = 'HTML';

问题根源 #

1. 笛卡尔积导致重复行：FROM子句中同时使用逗号连接[dbo].[vMailReworkPicking_2057]和vOuttakeOrderDetails_2057，这会生成两个表的笛卡尔积，即使单独查询时结果看似一行，实际在XML生成逻辑中会产生大量重复行。
2. 重复表引用：后续又通过LEFT JOIN再次关联vMailReworkPicking_2057，进一步放大了重复数据的问题。

修复后的代码 #

DECLARE @tableHTML NVARCHAR(max) = '';

SET @tableHTML =
'<h1>Picking en Rework</h1>' +
'<table border="1">' +
'<tr>' +
'<th>Order ETA</th>' +
'<th>To be Picked</th>' +
'<th>Rework Actions</th>' +
'<th>Destination Plts req order-line</th>' +
'<th>Destination Plts Req Total</th>' +
'<th>Reworked Plts On Stock</th>' +
'<th>Plts Left To Rework Order-Line</th>' +
'<th>Plts Left To Rework total</th>' +
'</tr>' +    
CAST
(   
( 
SELECT
                        
td = COALESCE(vOuttakeOrderHeaders_2057.[ETA], ''),  '', 
td = COALESCE(vMailReworkPicking_2057.[To be Picked], ''), '',             
td = COALESCE(vReworkReq_2057.[Rework Actions], ''),  '',
td = COALESCE(vReworkReq_2057.[Destination Plts Req Order-Line], ''), '',
td = COALESCE(vReworkReq_2057.[Destination Plts Req total], ''),  '',
td = COALESCE(vReworkReq_2057.[Reworked Plts On Stock], ''),  '',
td = COALESCE(vReworkReq_2057.[Plts Left To Rework Order-Line], ''), '',
td = COALESCE(vReworkReq_2057.[Plts Left To Rework total], ''),  ''
                                                             
FROM vOuttakeOrderDetails_2057
LEFT JOIN vOuttakeOrderHeaders_2057 
    ON vOuttakeOrderDetails_2057.iOrderHeaderSysID = vOuttakeOrderHeaders_2057.iOrderHeaderSysID
LEFT JOIN vReworkReq_2057 
    ON vOuttakeOrderDetails_2057.Article = vReworkReq_2057.Article
LEFT JOIN vMailReworkPicking_2057 
    ON vOuttakeOrderHeaders_2057.[ETA] = vMailReworkPicking_2057.[Order ETA]
WHERE 
    vOuttakeOrderHeaders_2057.[Order Type] LIKE 'outtake'
    AND DisposedQuantity < TargetQuantity 
    AND RestPick > 0
ORDER BY vOuttakeOrderHeaders_2057.[ETA] DESC
FOR XML PATH('tr'), TYPE) AS NVARCHAR(MAX)) +
'</table>';

EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'test123',
@recipients = 'email',
@subject = 'Picking & Rework',
@body = @tableHTML,
@body_format = 'HTML';

关键修改点 #

• 移除了FROM子句中重复的[dbo].[vMailReworkPicking_2057]引用，彻底避免笛卡尔积的产生
• 明确[To be Picked]字段的来源为vMailReworkPicking_2057表，消除字段歧义
• 保留原有业务逻辑的同时，确保所有表连接都基于明确的关联条件，避免无意义的重复数据

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