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

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

Cristian_Galindo — Thu, 12 Nov 2020 11:23:53 GMT

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?

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

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

Michael_Boyce — Fri, 13 Nov 2020 02:37:48 GMT

Hi @Cristian_Galindo ,

It seems that Database B was constructed on an earlier version of SQL that could be having an impact in the connection. Arc is not finding any Commit statements after it sends the changes through.

Here is an article from 2017 where the person is having the same/similar issue:

https://gis.stackexchange.com/questions/248064/arcsdesqlexecute-mismatching-number-of-begin-and-commit-statements

These may also be of help, but probably not as much:

https://support.esri.com/en/technical-article/000010759

https://docs.microsoft.com/en-us/sql/connect/odbc/windows/system-requirements-installation-and-driver-files?view=sql-server-ver15

Hope you find you answer,

Michael

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

Cristian_Galindo — Fri, 13 Nov 2020 13:20:16 GMT

The issue looks similar, but despite the fact that I receive the same error, in database A the changes are committed.

Reading the suggested links, I am doing both calls from the same computer, and i have both databases in the same instance of SQLServer.

I am going to try changing the compatibility of the database.