Silent rollback in transaction handling with TRY/CATCH block

[FranLegon]

Description:
When executing a query that includes a TRY/CATCH SQL block, the transaction appears to silently roll back without throwing any errors. This behavior causes statements prior to TRY/CATCH to be rolled back, while following statements are executed and committed.

Example code to reproduce:

import pyodbc

conn_string = 'DRIVER={ODBC Driver 18 for SQL Server};SERVER=azureserver.database.windows.net;DATABASE=testing_database;ENCRYPT=yes;UID=Tester;PWD=Testing123;ConnectionTimeout=150;MultipleActiveResultSets=True'
conn = pyodbc.connect(conn_string)
conn.autocommit = False
crsr = conn.cursor()


try:
    dropquery = "IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Testing]') AND type in (N'U')) DROP TABLE [dbo].[Testing]"
    crsr.execute(dropquery)

    working_createquery = """
    IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Testing]') AND type in (N'U'))
    BEGIN
        CREATE TABLE [dbo].[Testing](
            [TestInt] [INT] NULL
        )
    END;
    """
    notworking_createquery = """
    IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Testing]') AND type in (N'U'))
    BEGIN
        CREATE TABLE [dbo].[Testing](
            [TestInt] [INT] NULL
        )
    END;
    BEGIN TRY
        SELECT CAST('this will fail' AS INT)
    END TRY
    BEGIN CATCH
        PRINT('Hi, Im catch')
    END CATCH
    """
    createquery = notworking_createquery
    crsr.execute(createquery)

    insertquery = "INSERT INTO [dbo].[Testing] VALUES (1)"
    crsr.execute(insertquery)

    conn.commit()

except Exception as e:
    conn.rollback()
    print(f"Transaction failed: {e}")

finally:
    crsr.close()
    conn.close()

print("Done.")

Expected Behavior:
The transaction should either commit all statements or roll back all statements if an error occurs.

Observed Behavior:

• The DROP and CREATE statements are rolled back.
• The INSERT statement is executed and committed.
• No errors are thrown, and the transaction appears to silently roll back.

Environment:

• Python version: 3.11.2
• pyodbc version: 5.2.0
• ODBC Driver: ODBC Driver 18 for SQL Server
• Database: SQL Server (Azure)
• System: Linux AZUREVM 6.1.0-31-cloud-amd64 1 SMP PREEMPT_DYNAMIC Debian 6.1.128-1 (2025-02-07) x86_64 GNU/Linux

Silent rollback in transaction handling with TRYCATCH block.py.txt