exec into (insert into from stored procedure)后，从属过程失败时出现神秘错误：“当前事务无法提交”

Question

我在使用T-SQL / SQL服务器时遇到了一个问题(2017/140)：

在我的场景中，有许多存储过程(= worker过程)，它们依次执行不同的任务，最后使用select语句输出结果。

上级过程(= runner procedure)一个接一个地调用工人过程，并使用insert-into语句将其结果收集到表中。

辅助过程中的处理使用try-/catch语句进行保护，以便在其中一个辅助过程失败时不会中止运行器过程。还可以确保辅助过程始终返回结果，即使此过程在处理过程中遇到错误也是如此。

但是，如果在辅助过程中发生错误，则运行器过程会遇到错误，尽管该错误已经在辅助过程中被截获和处理。错误消息是：“当前事务无法提交，并且不支持写入日志文件的操作。回滚事务。“正如您从我的示例脚本中看到的那样，这里没有使用显式事务。

只有在使用Select-Into语句将辅助过程的结果传输到表中时，才会出现该问题。作为示例，我创建了另一个runner过程，它只执行worker过程，但不将其结果传输到表中。在这种情况下，将不会出现错误。

CREATE SCHEMA bugchase;
GO

DROP PROCEDURE IF EXISTS bugchase.worker_1;
DROP PROCEDURE IF EXISTS bugchase.worker_2;
DROP PROCEDURE IF EXISTS bugchase.runner_selectOnly;
DROP PROCEDURE IF EXISTS bugchase.runner_selectAndInsert;
GO

CREATE PROCEDURE bugchase.worker_1
AS
BEGIN
    -- this worker just returns a value
    SELECT
        'Result Worker 1';
END;
GO

CREATE PROCEDURE bugchase.worker_2
AS
BEGIN
    -- this worker encounters an error while processing.
    -- the error will be catched and some data will be returned

    DECLARE @result INT;

    BEGIN TRY
        -- this will force an error, because 'ABCD' could not be casted as float
        SET @result = CAST('ABCD' AS FLOAT);

    END TRY
    BEGIN CATCH
    -- just catch, don't do anything else

    END CATCH;

    SELECT
        'Result Worker 2';
END;
GO

CREATE PROC bugchase.runner_selectAndInsert
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @result AS TABLE (value NVARCHAR(64));

    -- exec worker_1 (no error will be thrown inside worker_1)
    PRINT 'Select&Insert: Worker 1 start';
    INSERT INTO @result (value)
    EXEC bugchase.worker_1;
    PRINT 'Select&Insert: Worker 1 end';

    -- exec worker_2 (will throw and catch an error inside)
    PRINT 'Select&Insert: Worker 2 start';
    INSERT INTO @result (value)
    EXEC bugchase.worker_2;
    PRINT 'Select&Insert: Worker 2 end';

END;
GO

CREATE PROC bugchase.runner_selectOnly
AS
BEGIN
    SET NOCOUNT ON;

    -- exec worker_1 (no error will be thrown inside worker_1)
    PRINT 'SelectOnly: Worker 1 start';
    EXEC bugchase.worker_1;
    PRINT 'SelectOnly: Worker 1 end';

    -- exec worker_2 (will throw and catch an error inside)
    PRINT 'SelectOnly: Worker 2 start';
    EXEC bugchase.worker_2;
    PRINT 'SelectOnly: Worker 2 end';


END;
GO

BEGIN TRY
    -- because all errors are catched within the worker-procedures, there should no error occur by this call
    EXEC bugchase.runner_selectAndInsert;
END TRY
BEGIN CATCH
    -- indeed an error will occur while running worker 2
    PRINT CONCAT('Select&Insert: ERROR:', ERROR_MESSAGE());
END CATCH;
GO

BEGIN TRY
    -- for demonstration only, this will run as expected
    EXEC bugchase.runner_selectOnly;
END TRY
BEGIN CATCH
    -- No error will occur
    PRINT CONCAT('SelectOnly: ERROR:', ERROR_MESSAGE());
END CATCH;
GO

DROP PROCEDURE bugchase.worker_2;
DROP PROCEDURE bugchase.worker_1;
DROP PROCEDURE bugchase.runner_selectOnly;
DROP PROCEDURE bugchase.runner_selectAndInsert;
DROP SCHEMA bugchase;

上面的脚本将显示以下结果：

Select&Insert: Worker 1 start
Select&Insert: Worker 1 end
Select&Insert: Worker 2 start
Select&Insert: ERROR:
The current transaction cannot be committed and cannot support operations that write to the log file.
Roll back the transaction.

当结果没有存储到表中时，不会出现错误：

SelectOnly: Worker 1 start
SelectOnly: Worker 1 end
SelectOnly: Worker 2 start
SelectOnly: Worker 2 end

Answer 1

正如Anton在评论中提到的，这是SET IMPLICIT_TRANSACTIONS文档中提到的SQL Server自动提交功能的副作用。基本上，使用IMPLICIT_TRANSACTIONS OFF (缺省)，有一堆语句自动包装在一个看不见的事务中。INSERT就是其中之一，所以在INSERT INTO EXEC中发生的任何事情都在这些自动提交事务中。您可以通过打印或在其中一个工作进程中选择@@trancount来亲自查看。

This other question有一些很好的解释，解释了为什么在捕获错误的情况下仍会发生不可提交的事务。

在您的例子中，您可以通过重写代码来从工作进程返回数据，而不使用insert into，从而解决此问题。一个简单的选择是将运行器进程中的表变量替换为临时表，然后将其插入到工作进程中的临时表中，而不仅仅是选择结果集。

例如，runner_selectAndInsert将变为：

CREATE PROC bugchase.runner_selectAndInsert
AS
BEGIN
    SET NOCOUNT ON;

    CREATE TABLE #result (value NVARCHAR(64));

    -- exec worker_1 (no error will be thrown inside worker_1)
    PRINT 'Select&Insert: Worker 1 start';
    EXEC bugchase.worker_1;
    PRINT 'Select&Insert: Worker 1 end';

    -- exec worker_2 (will throw and catch an error inside)
    PRINT 'Select&Insert: Worker 2 start';
    EXEC bugchase.worker_2;
    PRINT 'Select&Insert: Worker 2 end';

END;

而worker_1将会变成：

CREATE PROCEDURE bugchase.worker_1
AS
BEGIN
    -- this worker just returns a value
    INSERT INTO #result (value)
    SELECT
        'Result Worker 1';
END;

Erland Sommarskog here丰富了更多像这样在proc之间传递数据的选项。