Need help with a ArcPy export script failing! Thanks

NetAdminTeam · ‎06-09-2020

So we are running a ArcPy Export script that started failing. I am not super familiar with SQL, ArcGIS, and python but I think the intention of the script is to delete some tables and append them with new information. I have posted the script and error log is this will help. Thanks for any help in advance

Error Log:

16:05:25 Starting script
16:05:37 Opening Model
16:06:37 Processing Capacitor
16:06:37 Processing Regulator
16:06:37 Processing Transformer
16:06:37 Processing Switch
16:06:37 Processing Node
16:06:37 Processing Source
16:06:37 Processing Device
16:06:37 Processing Motor
16:06:37 Processing Generator
16:06:37 Processing Consumer
16:06:37 Processing Switchgear
16:06:37 Processing Pole
16:06:37 Processing Tower
16:06:37 Processing Pad
16:06:37 Processing Pedestal
16:06:37 Processing JunctionBox
16:06:37 Processing Enclosure
16:06:37 Processing Vault
16:06:37 Processing PullBox
16:06:37 Processing ForeignStructure
16:06:37 Processing Marker
16:06:37 Processing Light
16:06:37 Processing SurfaceStructure
16:06:37 Processing StorageLocation
16:06:37 Processing NonUtilityFacility
16:06:37 Processing Overhead
16:06:37 Processing Underground
16:06:39 Deleting records from C:\Milsoft\ArcPy Export\arcpy.sde\SDS_Milsoft_Geodatabase.DBO.Milsoft\StorageLocation
16:06:42 Appending records to C:\Milsoft\ArcPy Export\arcpy.sde\SDS_Milsoft_Geodatabase.DBO.Milsoft\StorageLocation
16:06:49 Deleting records from C:\Milsoft\ArcPy Export\arcpy.sde\SDS_Milsoft_Geodatabase.DBO.Milsoft\Transformer
16:06:54 Appending records to C:\Milsoft\ArcPy Export\arcpy.sde\SDS_Milsoft_Geodatabase.DBO.Milsoft\Transformer
16:08:00 Deleting records from C:\Milsoft\ArcPy Export\arcpy.sde\SDS_Milsoft_Geodatabase.DBO.Milsoft\JunctionBox
16:08:03 Appending records to C:\Milsoft\ArcPy Export\arcpy.sde\SDS_Milsoft_Geodatabase.DBO.Milsoft\JunctionBox
16:08:08 Deleting records from C:\Milsoft\ArcPy Export\arcpy.sde\SDS_Milsoft_Geodatabase.DBO.Milsoft\Generator
16:08:11 Appending records to C:\Milsoft\ArcPy Export\arcpy.sde\SDS_Milsoft_Geodatabase.DBO.Milsoft\Generator
16:08:15 Deleting records from C:\Milsoft\ArcPy Export\arcpy.sde\SDS_Milsoft_Geodatabase.DBO.Milsoft\Switchgear
16:08:18 Appending records to C:\Milsoft\ArcPy Export\arcpy.sde\SDS_Milsoft_Geodatabase.DBO.Milsoft\Switchgear
16:08:22 Deleting records from C:\Milsoft\ArcPy Export\arcpy.sde\SDS_Milsoft_Geodatabase.DBO.Milsoft\Source
16:08:25 Appending records to C:\Milsoft\ArcPy Export\arcpy.sde\SDS_Milsoft_Geodatabase.DBO.Milsoft\Source
16:08:30 Deleting records from C:\Milsoft\ArcPy Export\arcpy.sde\SDS_Milsoft_Geodatabase.DBO.Milsoft\Pad
16:08:36 ERROR 999999: Error executing function.
Underlying DBMS error [HY000:[Microsoft][ODBC Driver 17 for SQL Server]Unspecified error occurred on SQL Server. Connection may have been terminated by the server.] [SDS_Milsoft_Geodatabase.DBO.Pad][STATE_ID = 8256]
Failed to execute (DeleteRows).

Script:

# Import system modules

import os, gc

import arcpy

from arcpy import env

import Logging

try:

Logging.CleanPreviousFile()

Logging.LogMessage('Starting script')

env.overwriteOutput = True

#Set Model Path

env.workspace = "C:\\Milsoft\\Database\\Export Model.wm"

#Check if model is open

modelOpenFile = os.path.join(env.workspace, ".modelinuse")

if os.path.exists(modelOpenFile):

raise ValueError("Model is already open.")

#Set Output Path

#ex: "C:/output.gdb", "C:/output.sde", "C:/output", "C:/output.mdb"

outWorkspace = r"C:\Milsoft\ArcPy Export\arcpy.sde\SDS_Milsoft_Geodatabase.DBO.Milsoft"

#Get list of WMM Feature Classes

fcList = arcpy.ListFeatureClasses()

fcDict = {}

isOpen = False

# Create list of each WMM feature class path

for wmFeature in fcList:

if not isOpen:

Logging.LogMessage('Opening Model')

arcpy.GetCount_management(wmFeature)

isOpen = True

Logging.LogMessage('Processing {}'.format(wmFeature))

# Determine the new output feature class path and name

outFeatureClass = wmFeature

fcDict[wmFeature] = (outWorkspace, outFeatureClass)

for k, v in fcDict.iteritems():

#if k <> 'Transformer': continue

outFC = os.path.join(v[0], v[1])

if arcpy.Exists(outFC):

Logging.LogMessage('Deleting records from {}'.format(outFC))

arcpy.DeleteRows_management (outFC)

Logging.LogMessage('Appending records to {}'.format(outFC))

arcpy.Append_management(k, outFC, 'NO_TEST')

else:

Logging.LogMessage('Creating {}'.format(outFC))

arcpy.FeatureClassToFeatureClass_conversion(k, v[0], v[1])

gc.collect()

Logging.LogMessage('Completed export')

Logging.FlushLog()

except Exception as e:

Logging.LogErrorMessage(e.message)

Logging.FlushLog()

os.kill(os.getpid(), -9)

JoeBorgione · ‎06-09-2020

Perhaps you could post things instead of providing zip files.

That should just about do it....

NetAdminTeam · ‎06-09-2020

Sorry, fixed. For some reason it kept changing my attached files to zip files so I just posted the error and script to the thread

JoeBorgione · ‎06-09-2020

I like to break down a script into multiple try/except blocks; it makes it easier to figure out where things go south. That said, here is what your code looks like using the syntax highlighter You set a number of variables inside the try block that I would set well ahead of the try block. The only thing you need in the try/accept block is the actual geoprocessing step which I see is the delete rows and then the append; those two ought to be in their own try/except block.

Long story short, its line 58 that is bailing on you. The 99999 error is ambiguous, but the rest of the error message has meaning. When was the last time you compressed your egdb? Something is up with that state id of 8256.

# Import system modules
import os, gc
import arcpy
from arcpy import env
import Logging

 

try:
    Logging.CleanPreviousFile()
    Logging.LogMessage('Starting script')
    env.overwriteOutput = True
    #Set Model Path
    env.workspace = "C:\\Milsoft\\Database\\Export Model.wm"

    #Check if model is open
    modelOpenFile = os.path.join(env.workspace, ".modelinuse")
    if os.path.exists(modelOpenFile):
        raise ValueError("Model is already open.")
     #Set Output Path

    #ex: "C:/output.gdb", "C:/output.sde", "C:/output", "C:/output.mdb"

    outWorkspace = r"C:\Milsoft\ArcPy Export\arcpy.sde\SDS_Milsoft_Geodatabase.DBO.Milsoft"

 

    #Get list of WMM Feature Classes
    fcList = arcpy.ListFeatureClasses()
    fcDict = {}
    isOpen = False

    # Create list of each WMM feature class path
    for wmFeature in fcList:
        if not isOpen:
            Logging.LogMessage('Opening Model')
            arcpy.GetCount_management(wmFeature)
            isOpen = True

        Logging.LogMessage('Processing {}'.format(wmFeature))

        # Determine the new output feature class path and name
        outFeatureClass = wmFeature
        fcDict[wmFeature] = (outWorkspace, outFeatureClass)

       

    for k, v in fcDict.iteritems():

        #if k <> 'Transformer': continue

        outFC = os.path.join(v[0], v[1])

        if arcpy.Exists(outFC): 

            Logging.LogMessage('Deleting records from {}'.format(outFC))

            arcpy.DeleteRows_management (outFC)

            Logging.LogMessage('Appending records to {}'.format(outFC))

            arcpy.Append_management(k, outFC, 'NO_TEST')

      else:
            Logging.LogMessage('Creating {}'.format(outFC))
            arcpy.FeatureClassToFeatureClass_conversion(k, v[0], v[1])
     gc.collect()

    Logging.LogMessage('Completed export')

    Logging.FlushLog()

 

except Exception as e:
    Logging.LogErrorMessage(e.message)

   
Logging.FlushLog()
os.kill(os.getpid(), -9)‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

That should just about do it....

NetAdminTeam · ‎06-09-2020

I appreciate the input. I am going to be honest. I am not the person who wrote this script or even super well versed in Python. We just support employees that use ArcGIS and I am in charge of trying to figure out what is causing this issue. Are you talking about compressing the db this script is calling? Is there a set meaning for these state IDs? Thanks for any input!

JoeBorgione · ‎06-10-2020

It appears to me that the database accessed in this script is a 'versioned enterprise geodatabse' (what a mouthful); this allows mulitple editors to work in the same envrionement with their own 'version'. As these edits are made, the data base builds a 'tree' of states, starting from state zero. Over time, this state tree can grow into a behmouth; I've seen 100,000 states. You've got a least 8,200 states, which isn't crazy, but as mentioned, for whatever reason state 8256 (a branch on the tree) appears to have a problem.

If my observations/speculations are true (note my use of the word appears) you'll want to start with some typical database management tasks which inlude reconciling and posting all the versions to the 'default' version, and then performing a 'compress' on the data base.

Start with: An overview of traditional versioning—ArcGIS Help | Documentation As long as we are being honest here, it's long and involved.

That should just about do it....