Need help with a ArcPy export script failing! Thanks

281
5
06-09-2020 01:07 PM
NetAdminTeam
New Contributor

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)

0 Kudos
5 Replies
JoeBorgione
MVP Esteemed Contributor

Perhaps you could post things instead of providing zip files.

That should just about do it....
0 Kudos
NetAdminTeam
New Contributor

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

0 Kudos
JoeBorgione
MVP Esteemed Contributor

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....
0 Kudos
NetAdminTeam
New Contributor

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!

0 Kudos
JoeBorgione
MVP Esteemed Contributor

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....
0 Kudos