Transaction Mode Issues with SQL SDE Workspace

KirbyKalbaugh — Sat, 11 Dec 2021 10:01:21 GMT

I am getting the infamous transaction mode error in ArcGIS Pro running the code outside of ArcGIS Pro 2.4 using propy.bat. I feel like I've exhausted my Googling ability to find a solution that works. Any help is appreciated.

RuntimeError: workspace already in transaction mode

Here's my code:

import sys
import os
import arcpy
import pyodbc
import time
print("Starting")
import importlib.util
import config.py as Config

now = datetime.datetime.now()
strDate = str(now.year) + str(now.month) + str(now.day) + str(now.hour) + str(now.minute) + str(now.second)

arcpy.env.overwriteOutput = True

# Setup Verion
SDE = Config.remote_db_host+".sde" # SDE File Base on server name
parentVersion = "dbo.DEFAULT"
versionName = "temp"+ strDate
arcpy.CreateVersion_management(SDE, parentVersion, versionName, "PUBLIC")
print("Version Created")

# Setup Temp Workspace
temploc = r"c:\temp"
file = "tmp.sde"
workspace = os.path.join (temploc, file)
print("Workspace: {}".format(workspace),'\n',flush=True)

if os.path.isfile(workspace):
    print("Deleting Existing SDE File",'\n',flush=True)
    os.remove(workspace)

arcpy.CreateDatabaseConnection_management(temploc,file,"SQL_SERVER",Config.remote_db_host,'DATABASE_AUTH',Config.remote_db_user,Config.remote_db_password,"SAVE_USERNAME",Config.remote_db_name,"","TRANSACTIONAL","dbo.DEFAULT")

arcpy.env.workspace = workspace

desc = arcpy.Describe(workspace)
cp = desc.connectionProperties
print("Database Connection Properties:")
print("%-12s %s" % ("        Server:", cp.server))
print("%-12s %s" % ("      Instance:", cp.instance))
print("%-12s %s" % ("      Database:", cp.database))
print("%-12s %s" % ("Is GeoDatabase:", cp.is_geodatabase))
print("%-12s %s" % ("          User:", cp.user))
print("%-12s %s" % ("       Version:", cp.version))
print("%-12s %s" % ("Workspace Type:", desc.workspaceType))

edit = arcpy.da.Editor(workspace)
edit.startEditing(True,False)
if edit.isEditing:
    print("You are in an edit session")
else:
    print("You aren't in an edit session")
    sys.exit
edit.startOperation()


con = pyodbc.connect('DRIVER={SQL Server Native Client 11.0};SERVER='+str(Config.remote_db_host)+';DATABASE='+str(Config.remote_db_name)+';Uid='+str(Config.remote_db_user)+';PWD='+str(Config.remote_db_password))
cur = con.cursor()
# Get Coordinates from another table to add to this feature layer
cur.execute("SELECT t1.OBJECTID,t1.GROUP_OUTLINE_COORDS FROM PLANTS t1 LEFT JOIN MASSPLANTINGS t2 ON t2.PLANTID = t1.OBJECTID WHERE t2.PLANTID IS NULL AND t1.GROUP_OUTLINE_COORDS IS NOT NULL AND CURRENT_CONDITION != 'D' AND CURRENT_CONDITION != 'R' AND CURRENT_CONDITION != 'U'")
data = cur.fetchall()
del cur
print("Connected to SQL Information Source Server")
feature = os.path.join (workspace, Config.feature_dataset,Config.massplantsdb)
arcpy.MakeFeatureLayer_management (feature, "tmplyr")
fieldsToUpdate = ['SHAPE@','PLANTID']

with arcpy.da.InsertCursor("tmplyr", fieldsToUpdate) as icur:
    print("Received SQL Data")
    counter = 0
    group_coords = arcpy.Array()
    length = len(data)
    for plant in data:
        counter+=1
        coord = plant[1].split(' ')
        for x, y in zip(coord[0::2], coord[1::2]):
            group_coords.add(arcpy.Point(float(x),float(y)))
        icur.insertRow([arcpy.Polygon(group_coords),plant[0]])
        group_coords.removeAll()
    print('Done')

# Cleanup the cursor if necessary
try:
    del cur
    print("Deleted cur")
except:
    print('No Cursor')
edit.stopOperation()
edit.stopEditing(True)

arcpy.ChangeVersion_management('tmplyr', "TRANSACTIONAL", parentVersion)

arcpy.ReconcileVersions_management (workspace, "", parentVersion, versionName, with_post = "POST", with_delete = "DELETE_VERSION")
arcpy.Delete(workspace)
os.remove(workspace)
‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

I am using basically this exact same code for point data and it works without having use the editor or versioning.

Here's the output from above with a little bit changed to "protect the innocent"

c:\folder\propy.bat plants_to_mass_plantings.py
Starting
Version Created
Workspace: c:\temp\tmp.sde

Deleting Existing SDE File

Database Connection Properties:
        Server: sqlserver.domainname.com
      Instance: sde:sqlserver:sqlserver.domainname.com
      Database: arboretum
Is GeoDatabase: true
          User: db_admin
       Version: dbo.DEFAULT
Workspace Type: RemoteDatabase
You are in an edit session
Connected to SQL Information Source Server
Received SQL Data
Traceback (most recent call last):
  File "plants_to_mass_plantings.py", line 78, in <module>
    icur.insertRow([arcpy.Polygon(group_coords),plant[0]])
RuntimeError: workspace already in transaction mode‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

topic Transaction Mode Issues with SQL SDE Workspace in Python Questions

Transaction Mode Issues with SQL SDE Workspace