Bug: CalculateField errors after AlterField Tool

[ShaneBuscher]

There looks to be a bug with running the CalculateField tool directly after the AlterField tool via arcpy. AlterField runs successful, but as shown in red below, CalculateField fails with a 'column not found' error. The column listed in the error is not what was altered, it always happens to be the first column in the feature class. 

The environment is ArcGIS 10.3.1, 64-bit Python, ArcSDE 10.3.1 on SQL Server 2008. For security\privacy I replaced the arguments in the script. You can substitute your own SDE data to get the same result. 

# Script

import arcpy

arcpy.env.workspace = "../some_sde_connection.sde"

arcpy.AlterField_management("InputFeatureClass", "OriginalFieldName", "NewFieldName", "NewFieldName")

arcpy.CalculateField_management("InputFeatureClass", "AnotherField", "Foo", "PYTHON_9.3")

C:\projects\>alter_table_bug.py
Traceback (most recent call last):
File "C:\projects\OPS_Int_ArcGIS\source\scripts\alter_table_bug.py", line 15, in <module>
"PYTHON_9.3")
File "C:\Program Files (x86)\ArcGIS\Desktop10.3\ArcPy\arcpy\management.py", line 3457, in CalculateField
raise e
arcgisscripting.ExecuteError: ERROR 999999: Error executing function.
Attribute column not found [42S22:[Microsoft][SQL Server Native Client 11.0][SQL Server]Invalid column name 'TheFirstColumnInTheTable'.] [Database.SchemaName.InputFeatureClass][STATE_ID = 0]
Failed to execute (CalculateField).

[DanPatterson_Retired]

can you rule out a slow server by putting a 'time.sleep(2)' (import time first) into your script.  The field names may be changed, but I can't remember if the order is altered or what the alias is changed to.  If a script nap doesnt work, try to separate the process completely.  If sleep and separation doesn't work, then there is a deeper issue.

[ShaneBuscher]

Thanks for the tip Dan but no dice. Even with a delay I get the same error. I also feel that any time a developer needs to put a time delay to make a vendor api work means there is a bug in that api. 

[JoshuaBixby · Accepted solution]

Check out Clear Workspace Cache.  When connecting to enterprise geodatabases, ArcGIS (not just ArcPy) caches information about the enterprise geodatabase to improve interaction with the geodatabase.  Although I can't say specifically what gets cached (I know there are some who contribute to the forums who could get much more specific), it is likely that table schema are part of it.  Since you made changes to a table's schema and then tried to process data in the table, it kicks off that error.  I can't remember if this only happens in the application itself or when you run a standalone script as well.

[MichaelVolz]

Based on Joshua's suggestion, you might try duplicating your process against a file geodatabase instead of SDE where there probably would be no caching.  If it works in the file geodatabase, then it would most likely (not definitely) be the caching of SDE that is causing your issue.

[ShaneBuscher]

Cleared the cache and still getting the error. This is looking more like an official bug. 

[MichaelVolz]

Take a look at this post:

Pitfalls in ArcPy (2) 

It seems to me that you might need to run a script where you alter the field and then close the SDE connection.  Then run a second script where the SDE names have been updated so the CalculateField sees the new name of the field.

[ShaneBuscher]

I agree in theory with this, and will try. However, I consider this a bug if the AlterField can't be chained with another tool like CalculateField in the same script. If it's not supported, ESRI should give you a graceful message back indicating so instead of a hard exception. Will get back to you on whether or not this works though. 

[ShaneBuscher]

OK got the CalculateField working after AlterField using ClearWorkspaceCache in between. This didn't work the first time I tried because Joshua Bixby was also correct on the CalculateField- I had the 'expression' parameter incorrectly assigned. It was "foo" and needed to be "'foo'". ClearWorkspaceCache was definitely needed to be run to make both these tools (with proper params) work back-to-back.  Thanks!

[ShaneBuscher]

Joshua I unsuccessfully tried to clear the workspace as suggested and received an error of a different kind. Note that I also set the workspace again after clearing it. I believe the vague exception is still a result of the AlterTable tool leaving the feature class in an invalid state. 

C:\projects\OPS_Int_ArcGIS\source\scripts>alter_table_bug.py
Traceback (most recent call last):
File "C:\projects\OPS_Int_ArcGIS\source\scripts\alter_table_bug.py", line 20, in <module>
"PYTHON_9.3")
File "C:\Program Files (x86)\ArcGIS\Desktop10.3\ArcPy\arcpy\management.py", line 3457, in CalculateField
raise e
arcgisscripting.ExecuteError: ERROR 000539: SyntaxError: unexpected EOF while parsing (<expression>, line 1)
Failed to execute (CalculateField).