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

482
2
11-12-2020 03:23 AM
Cristian_AndresGalindo_Londoño
Occasional Contributor III

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

Tags (3)
0 Kudos
2 Replies
Michael_Boyce
New Contributor

Hi @Cristian_AndresGalindo_Londoño ,

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-comm...

 

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-drive...

 

Hope you find you answer,

 

Michael

0 Kudos
Cristian_AndresGalindo_Londoño
Occasional Contributor III

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.

0 Kudos