Mysterious error after exec into (insert into from stored procedure) when subordinate procedure fails: "current transaction cannot be committed"

Question

I encountered a problem using T-SQL / SQL server (2017/140):

In my scenario there are a number of stored procedures (= worker procedures), which in turn perform different tasks and at the end output the result with a select statement.

A superordinate procedure (= runner procedure) calls the worker procedures one after the other and collects their result in a table using an insert-into statement.

Processing within the worker procedures is secured with try-/catch statements so that the runner procedure is not aborted if one of the worker procedures should fail. It is also ensured that the worker procedures always return a result, even if this procedure encounters an error during processing.

However, the runner procedure encounters an error if an error has occurred in a worker procedure, although this error has already been intercepted and handled in the worker procedure. The error message is: „The current transaction cannot be committed and cannot support operations that write to the log file. Roll back the transaction.“ As you can see from my example script, no explicit transactions are used there.

The problem only occurs when the results of the worker procedures are transferred to a table with a Select-Into statement. As an example I have created another runner procedure, which only executes the worker procedures, but does not transfer their results to a table. In this case there will be no errors.

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;

The script above will show the following result:

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.

No error occurs, when the results are not stored into a table:

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

user_8487531 · Answer

As Anton mentioned in the comments, this is a side effect of the autocommit functionality of SQL Server which is mentioned in the documentation for SET IMPLICIT_TRANSACTIONS. Basically, with IMPLICIT_TRANSACTIONS OFF (the default), there are a bunch of statements that are automatically wrapped in an unseen transaction. INSERT is one of them, so anything that happens within the INSERT INTO EXEC is in one of these autocommit transactions. You can see this for yourself by printing or selecting @@trancount within one of the worker procs.

This other question has some great explanations of why the uncommittable transaction occurs even though you are catching the error.

In your case, you can work around this by rewriting the code to return data from the worker procs without using insert into. One simple option would be to replace your table variable in the runner proc with a temp table, then insert into that temp table within the worker procs instead of just selecting the result set.

e.g runner_selectAndInsert would become:

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;

and worker_1 would become:

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

Some more options for passing data between procs like this are fleshed out by Erland Sommarskog here.

Mysterious error after exec into (insert into from stored procedure) when subordinate procedure fails: "current transaction cannot be committed"

1 Answers