SSMS作业生成HTML报表SQL脚本报错排查请求

错误原因分析 #

1. CTE语法要求未满足：SQL Server中，以WITH开头的公用表表达式（CTE），其前一条语句必须以分号结尾，原脚本中CAST((前的行缺少分号，触发语法错误。
2. 无效XML元素名：原查询使用td1、td2作为列别名，FOR XML会生成<td1>、<td2>这类以数字开头的XML元素，不符合XML规范，导致语法错误。
3. 冗余分组逻辑：Totals和AllData执行了完全重复的分组操作，属于冗余代码，降低查询效率。
4. HTML标签顺序错误：原脚本中</font>标签在</table>之前，不符合HTML嵌套规则。

修正后的完整脚本 #

DECLARE @DataInicialMesAnterior DATE, @DataFinalMesAnterior DATE;
SET @DataInicialMesAnterior = DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) - 1, 0);
SET @DataFinalMesAnterior = DATEADD(DAY, -1, DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0));

DECLARE @tableHTML2 NVARCHAR(MAX), @corpo2 AS NVARCHAR(50);
SET @tableHTML2 =
    N'<font size="1"><H1>Produção Mensal Tecelagem Entre ' + CONVERT(VARCHAR, @DataInicialMesAnterior, 104) + ' - ' + CONVERT(VARCHAR, @DataFinalMesAnterior, 104) + '  </H1> ' +
    N'<style> th {color:#FFFFFF; background-color: #6193AC } </style>' +
    N'<table border="1" cellpadding="3" cellspacing="0" style="width: 100%">' +
    N'<tr><th rowspan="3">Maquina</th><th rowspan="3">Artigo</th><th rowspan="3">Descrição</th><th rowspan="3">Total Metros</th><th rowspan="3">Total Kgs</th></tr>' +
    N'<tr></tr><tr><th></th></tr>' +
    CAST((; -- 添加分号满足CTE语法要求
        WITH ProductionData AS (
            SELECT 
                'Tear - ' + LTRIM(RTRIM(STR(CAST(SUBSTRING(BI.U_MED1, 2, 2) AS INT)))) AS Maquina, 
                BI.REF AS Artigo, 
                ISNULL(ST.design, '') AS Descricao,
                SUM(CASE WHEN stobs.u_dobramal=1 THEN BI.QTT*2 ELSE BI.QTT END) AS MetrosProduzidos,
                SUM(CAST(BI.U_MED3 AS FLOAT)) AS KgsProduzidos
            FROM carlom.dbo.BI bi (NOLOCK)
            LEFT JOIN carlom.dbo.st ST (NOLOCK) ON ST.ref = BI.REF
            INNER JOIN carlom.dbo.stobs stobs (NOLOCK) ON ST.ref = stobs.ref 
            WHERE BI.NDOS = 20 
                AND BI.u_data2 BETWEEN @DataInicialMesAnterior AND @DataFinalMesAnterior
            GROUP BY 'Tear - ' + LTRIM(RTRIM(STR(CAST(SUBSTRING(BI.U_MED1, 2, 2) AS INT)))), BI.REF, ST.design
        ), OrderedData AS (
            SELECT 
                Maquina, 
                Artigo, 
                Descricao,
                MetrosProduzidos,
                KgsProduzidos,
                0 AS TotalOrder
            FROM ProductionData
            WHERE MetrosProduzidos > 0 OR KgsProduzidos > 0
            UNION ALL
            SELECT 
                'Total' AS Maquina, 
                '' AS Artigo, 
                '' AS Descricao,
                SUM(MetrosProduzidos) AS MetrosProduzidos,
                SUM(KgsProduzidos) AS KgsProduzidos,
                1 AS TotalOrder
            FROM ProductionData
        )
        SELECT 
            '<td>' + Maquina + '</td>' AS [*],
            '<td>' + Artigo + '</td>' AS [*],
            '<td>' + Descricao + '</td>' AS [*],
            '<td>' + CONVERT(VARCHAR, MetrosProduzidos) + '</td>' AS [*],
            '<td>' + CONVERT(VARCHAR, KgsProduzidos) + '</td>' AS [*]
        FROM OrderedData
        ORDER BY 
            TotalOrder,
            CASE WHEN ISNUMERIC(SUBSTRING(Maquina, LEN('Tear - ') + 1, LEN(Maquina))) = 1 THEN CAST(SUBSTRING(Maquina, LEN('Tear - ') + 1, LEN(Maquina)) AS INT) ELSE 99999 END, 
            Maquina
        FOR XML PATH(''), TYPE
    ).value('.', 'NVARCHAR(MAX)')) + N'</table></font>'; -- 调整HTML标签顺序，先关闭table再关闭font

SET @corpo2 = 'Produção do Mês';

EXEC msdb.dbo.sp_send_dbmail
    @profile_name = 'PHC',
    @recipients = 'david.sousa@bnextconsulting.com',
    @subject = @corpo2,
    @body = @tableHTML2,
    @body_format = 'HTML';

关键修正说明 #

1. CTE前置分号：在CAST((后添加分号，符合SQL Server对CTE的语法要求。
2. 替换列别名：将原有的td、td1、td2替换为有意义的名称，避免生成无效XML元素。
3. 显式生成标签：在最终查询中直接拼接<td>标签，通过FOR XML PATH('')生成合法的表格行结构。
4. 简化分组逻辑：移除冗余的Totals和AllData CTE，直接过滤有效数据后合并总计行，提升查询效率。
5. 修正HTML标签顺序：将</font>移至</table>之后，符合HTML嵌套规则。

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