SQLServer Transaction error when called from python

Question

I'm facing an incredibly puzzling situation, we have a recently updated SQL Server from 2016 to 2019, on which a stored procedure usually called from a python script after some data integration, now fails with an error.

"[25000] [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Cannot roll back TR_NL. No transaction or savepoint of that name was found. (6401)

The stored procedure itself follows a quite standard TRY/CATCH structure

USE [MYBASE]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[p_myproc] 
    @error NVARCHAR(MAX)= 'Success' OUTPUT
AS
BEGIN
SET NOCOUNT ON;
DECLARE @idMarche;
DECLARE @TranName VARCHAR(20);

IF EXISTS(SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = N'SOME_TABLE')
BEGIN 
    SELECT @TranName = 'TR_NL';
    BEGIN TRANSACTION @TranName;
    BEGIN TRY
        /*
            Bunch of updates, inserts etc. Some of which conditionals with nested IF BEGIN END etc.
        */
    END TRY
    BEGIN CATCH
        SELECT @error ='Error Number: ' + ISNULL(CAST(ERROR_NUMBER() AS VARCHAR(10)), 'NA')     + '; ' + Char(10) + 
                    'Error Severity '   + ISNULL(CAST(ERROR_SEVERITY() AS VARCHAR(10)), 'NA')   + '; ' + Char(10) +
                    'Error State '      + ISNULL(CAST(ERROR_STATE() AS VARCHAR(10)), 'NA')      + '; ' + Char(10) +
                    'Error Line '       + ISNULL(CAST(ERROR_LINE() AS VARCHAR(10)), 'NA')       + '; ' + Char(10) +
                    'Error Message '    + ISNULL(ERROR_MESSAGE(), 'NA')
        IF @@TRANCOUNT > 0
            ROLLBACK TRANSACTION @TranName;
    END CATCH
    IF @@TRANCOUNT > 0
        COMMIT TRANSACTION @TranName;
END

/*
    A few more similar blocks of conditional transactions
*/

IF @error = 'Success' OR @error IS NULL
BEGIN
    /*
        Drop some tables
    */
END
END

Following call works perfectly well in SSMS but fails with said error when sent from my python script

SET NOCOUNT ON;
DECLARE @return_value INTEGER;
DECLARE @error NVARCHAR(MAX);
EXEC  @return_value = [dbo].[p_myproc] @error = @error OUTPUT;
SELECT  @error AS erreur, @return_value AS retour;

The error could be raised if the procedure rolls back while executed in a parent/outer transaction: https://dbfiddle.uk/RrzZ4djN — lptr, Sep 09 '22 at 10:30

score 2 · Accepted Answer · edited Oct 09 '22 at 14:50

The problem is that Python be default runs all commands in a transaction, unless you set autocommit=true. This means it is trying to roll back your transaction, but your error handler has done that already.

Your error handler is in any case flawed in a number of ways:

As mentioned, it doesn't handle nested transactions well.
It swallows exceptions, then selects the error message. This means that the client side code is not recognizing that an exception has occurred.
If there were multiple errors at the same time (common with DBCC and BACKUP commands) then only one is returned.

Instead, just use SET XACT_ABORT ON; at the top of your procedure, if you need to do any cleanup, make sure to re-throw the original exception using THROW;. Do not rollback the transaction, it will be rolled back automatically.

CREATE OR ALTER PROCEDURE [dbo].[p_myproc]
AS

SET NOCOUNT ON;
SET XACT_ABORT ON;

BEGIN TRY;

    IF EXISTS(SELECT 1
        FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = N'SOME_TABLE')
    BEGIN 
        BEGIN TRANSACTION;
            /*
                Bunch of updates, inserts etc. Some of which conditionals with nested IF BEGIN END etc.
            */
        COMMIT TRANSACTION;
    END;

    /*
        A few more similar blocks of conditional transactions
    */

    /*
        Drop some tables
    */
END TRY
BEGIN CATCH
    -- do cleanup. Do NOT rollback
    ;
    THROW;   -- rethrows the original exception
END CATCH;

If no cleanup is needed then do not use a CATCH at all. XACT_ABORT will rollback the transaction anyway.

CREATE OR ALTER PROCEDURE [dbo].[p_myproc]
AS

SET NOCOUNT ON;
SET XACT_ABORT ON;


IF EXISTS(SELECT 1
    FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = N'SOME_TABLE')
BEGIN 
    BEGIN TRANSACTION;
        /*
            Bunch of updates, inserts etc. Some of which conditionals with nested IF BEGIN END etc.
        */
    COMMIT TRANSACTION;
END

/*
    A few more similar blocks of conditional transactions
*/

/*
    Drop some tables
*/

SQLServer Transaction error when called from python

1 Answers1