Transaction Mode Issues with SQL SDE Workspace

197
0
12-20-2019 01:48 PM
KirbyKalbaugh
New Contributor

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‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍
0 Kudos
0 Replies