ArcSDESQLExecute is not able to DELETE rows in one database but works in other

Question

I have two ESRI enterprise geodatabases on SQLServer: A and B. They are almost the same database (structure)

I have this code snippet in python to deleted several rows from versions:

def delete_project_features(edit_workspace, delete_tables, version, inspection_id):
    sde_conn = arcpy.ArcSDESQLExecute(edit_workspace)
    
    sde_conn.execute("EXEC sde.set_current_version '{0}';".format(version))
    sde_conn.execute("EXEC sde.edit_version '{0}',1 WITH RESULT SETS NONE;".format(version))

    for table in delete_tables:        
        inspectionId = inspection_id
        feature_count = sde_conn.execute("SELECT COUNT(*) FROM {0}_evw WHERE INSPECTION_ID = '{1}';".format(table, inspectionId))
        sde_conn.execute("DELETE FROM {0}_evw WHERE INSPECTION_ID = '{1}';".format(table, inspectionId))        
    try:        
        sde_conn.execute("EXEC sde.edit_version '{0}',2 WITH RESULT SETS NONE;".format(version))
        
    except Exception as e:
        if repr(e).find("error 266") > -1:
            logEngine.log(_("Deleted all redundant features"), logger, SERVICE)
        else:
            mess = "Failed to delete features"
            raise Exception(mess)
        

When I run it on Database A, the rows are deleted as expected, but in Database B the rows remain there.

I always get the error:

AttributeError: ArcSDESQLExecute: StreamExecute ArcSDE Extended error 266 [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Transaction count after EXECUTE indicates a mismatching number of BEGIN and COMMIT statements. Previous count = 1, current count = 0.

despite the fact of the error, the rows are deleted on A

What setting could be different in the databases that prevents in B the successful result of the script execution?

Noted differences so far: Database A: Compatibility level 2016, Recovery model Simple, Collation SQL_Latin1_General_CP1_CI_AS.

Database B: Compatibility level 2012, Recovery model Full, Collation Latin1_General_CI_AS.

EDIT: The SQL commands run with any issue if they are run from SSMS.

The EGDB release for both is 10.7

Answer 1

After creating a ticket to ESRI, this was their answer:

this error has been reported already on a different occasion. Esri recommended to apply "two" workarounds:......

...The error reported can be avoided by adding WITH RESULT SETS NONE: eGIS_Conn.execute("EXEC dbo.edit_version @name = 'dbo.Ch1', @edit_action=1 WITH RESULT SETS NONE ")

However, because these versioned view stored procedures issue a hard commit to commit all the nested transactions, another error can be encountered unless the entire set of SQL is executed in batch: eg: eGIS_Conn = arcpy.ArcSDESQLExecute(eGIS) eGIS_Conn.execute("EXEC dbo.set_current_version @version_name = 'dbo.Ch1' ; EXEC dbo.edit_version @name = 'dbo.Ch1', @edit_action=1 WITH RESULT SETS NONE; EXEC dbo.edit_version @name = 'dbo.Ch1', @edit_action=2 WITH RESULT SETS NONE")

The reason is that ArcSDE will by default always be in transaction. If the execute statements are not put in batch (like shown above) the transaction count will be different before and after execute the edit_version stored procedure and you will encounter the following error.

Therefore, the script was modified to this:

def delete_project_features(edit_workspace, delete_tables, version, inspection_id):
    sde_conn = arcpy.ArcSDESQLExecute(edit_workspace)
    sql_enable_edit = "EXEC sde.edit_version '{0}',1 WITH RESULT SETS NONE;".format(version)
    sql_commit_edits = "EXEC sde.edit_version '{0}',2 WITH RESULT SETS NONE;".format(version)
    sql_delete_base = """
            SET NOCOUNT ON;
            {0}
            BEGIN TRAN;
            {1}
            COMMIT TRAN;
            {2}
            """
    sde_conn.execute("EXEC sde.set_current_version '{0}';".format(version))
    try:
        for table in delete_tables:                            
            feature_count = sde_conn.execute("SELECT COUNT(*) FROM {0}_evw WHERE INSPECTION_ID = '{1}';".format(table, inspection_id))
            sql_delete_rows = "DELETE FROM {0}_evw WHERE INSPECTION_ID = '{1}';".format(table, inspection_id)

            sql_delete = sql_delete_base.format(sql_enable_edit, sql_delete_rows, sql_commit_edits)
            sde_conn.execute(sql_delete)
            logEngine.log(_("{0} features are deleted from {1}").format(feature_count, table), logger, SERVICE)
    except Exception:
        mess = "Failed to delete features"
        logEngine.log(_(mess), logger, SERVICE)
        raise Exception(mess)
    finally:
        sde_conn.execute("EXEC sde.set_default;")

Now, there is no Exception 266. It was found tha the SET NOCOUNT ON was required to a successful execution.