topic Re: Error using TOP and * in arcpy.da.SearchCursor in Python Questions

Error using TOP and * in arcpy.da.SearchCursor

FredSpataro — Sat, 11 Dec 2021 14:41:37 GMT

Hi All,

I've found an issue with using 'TOP N' in the sql_clause parameter and the default * wildcard for fields when executing a arcpy.da.SearchCursor. The resulting error message:

An expected Field was not found or could not be retrieved properly. [TOP 1 OBJECTID]

ArcGIS 10.6
Occurs in both ArcCatalog python window and 64-Bit python console (ArcGISx64106\python.exe)
Database is SQL Server 2017, licensed geodatabase
Occurs ONLY in registered Feature Classes....
- I have an assortment of versioned, archived and non-versioned/non-archived, in feature dataset, non-feature dataset, all fail with the same error.
- I have 1 registered table in the geodatabase that does NOT fail and a number of other non-registered/non-objectid tables that also do NOT fail.
- I have a few non-registered spatial tables with a generic column call OBJECTID that do NOT fail
I tried a couple different geodatabases in the same server, all fail same way. I don't have a second database server to test right now.
The generic SELECT TOP 1 * FROM FC(_evw) works fine in SSMS
Works fine if I include a specific fields list... but that requires extra code step and performance hit to run "describe"

fc = "YourDatabaseConnectionFile.sde/Database.Schema.FeatureClass"
sql_clause = ("TOP 1", None)
with arcpy.da.SearchCursor(fc, "*", None, None, False, sql_clause) as rows:
    row = rows.next()
    print row

Runtime error 
Traceback (most recent call last):
 File "<string>", line 2, in <module>
RuntimeError: An expected Field was not found or could not be retrieved properly. [TOP 1 OBJECTID]‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

Thanks Fred

Re: Error using TOP and * in arcpy.da.SearchCursor

FredSpataro — Sat, 11 Dec 2021 14:41:40 GMT

When I use describe to get the explicit list of fields it will still fails:

desc = arcpy.Describe(fc)
fields = [f.name for f in desc.fields]
with arcpy.da.SearchCursor(fc, fields, None, None, False, sql_clause) as rows:
     row = rows.next()
     
Runtime error 
Traceback (most recent call last):
  File "<string>", line 2, in <module>
RuntimeError: An expected Field was not found or could not be retrieved properly. [TOP 1 OBJECTID]‍‍‍‍‍‍‍‍‍

Re: Error using TOP and * in arcpy.da.SearchCursor

FredSpataro — Sat, 11 Dec 2021 14:41:42 GMT

One more:

It appears to be one of the "SHAPE" fields [SHAPE, SHAPE.STArea(), SHAPE.STLength()]... this will work:

desc = arcpy.Describe(fc)
fields = [f.name for f in desc.fields if not f.name.startswith("SHAPE")]‍
with arcpy.da.SearchCursor(fc, fields, None, None, False, sql_clause) as rows:
     row = rows.next()‍‍‍‍

Re: Error using TOP and * in arcpy.da.SearchCursor

JoshuaBixby — Sat, 11 Dec 2021 14:41:45 GMT

Something is definitely up with the SHAPE field and using TOP with SQL Server. I see similar results when I test it; and worse, I crash ArcCatalog or ArcMap if I try the code using just the SHAPE field. Seems like a defect to me.

In the mean time, instead of using TOP 1 to retrieve only 1 row, you can just create the cursor and grab the first record and move on, like:

fc = "YourDatabaseConnectionFile.sde/Database.Schema.FeatureClass"
with arcpy.da.SearchCursor(fc, "*") as rows:
    print(next(rows))


‍‍‍‍‍

Re: Error using TOP and * in arcpy.da.SearchCursor

FredSpataro — Thu, 13 Dec 2018 17:30:49 GMT

Josh, thanks for verifying the issue. I'll submit to tech support.