使用Pentaho Execute Row SQL Script时为何无法捕获SQL错误？

问题1：多命令SQL的后续错误未被捕获 #

在SSMS中执行包含多个命令的SQL时，若后续命令触发错误（比如主键冲突），会直接终止进程并返回错误；但用Pentaho的Execute Row SQL脚本节点执行完全相同的脚本时，只要第一个命令执行成功，整个转换任务就会被标记为完成，完全忽略后续命令的错误。

示例SQL：

INSERT INTO dbo.table (ID, Name) VALUES (3, 'Anna')
INSERT INTO dbo.table (ID, Name) VALUES (3, 'Nathan')

问题2：分号分隔命令导致动态变量失效 #

为了捕获后续错误尝试用分号分隔命令，但当脚本包含动态变量时，后续代码块无法识别已声明的变量，报错：

必须声明标量变量@common_name

示例SQL：

DECLARE @common_name VARCHAR(400) = 'Testface';

INSERT INTO dbo.table (ID, Name) VALUES (3, @common_name);
INSERT INTO dbo.table (ID, Name) VALUES (3, @common_name);

可行的解决思路 #

1. 启用SQL Server的XACT_ABORT选项 #

在动态脚本开头添加SET XACT_ABORT ON;，这个选项会强制SQL Server在遇到错误时终止整个批处理并回滚事务，确保后续错误能被Pentaho捕获。同时该选项不会破坏变量的作用域，整个脚本仍属于同一个批处理。

修改后的示例：

SET XACT_ABORT ON;
INSERT INTO dbo.table (ID, Name) VALUES (3, 'Anna')
INSERT INTO dbo.table (ID, Name) VALUES (3, 'Nathan')

2. 保持脚本为单个批处理，避免分号拆分 #

不要用分号将脚本拆分为多个独立批处理，保持整个脚本作为一个完整的批处理块，结合XACT_ABORT选项即可同时保证错误捕获和变量有效性。

针对带变量的脚本示例：

SET XACT_ABORT ON;
DECLARE @common_name VARCHAR(400) = 'Testface';

INSERT INTO dbo.table (ID, Name) VALUES (3, @common_name)
INSERT INTO dbo.table (ID, Name) VALUES (3, @common_name)

3. 检查Pentaho节点的错误处理配置 #

确认Execute Row SQL脚本节点的「出错时停止」选项已启用，同时检查转换的全局错误处理设置，确保没有忽略SQL执行错误的配置。

4. 用TRY-CATCH块显式抛出错误 #

在动态SQL中加入TRY-CATCH块，捕获错误后重新抛出，确保Pentaho能接收到错误信息：

BEGIN TRY
    SET XACT_ABORT ON;
    DECLARE @common_name VARCHAR(400) = 'Testface';

    INSERT INTO dbo.table (ID, Name) VALUES (3, @common_name)
    INSERT INTO dbo.table (ID, Name) VALUES (3, @common_name)
END TRY
BEGIN CATCH
    THROW;
END CATCH

内容的提问来源于stack exchange，提问作者ned-codes