Error using TOP and * in arcpy.da.SearchCursor

Question

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 "", line 2, in <module> RuntimeError: An expected Field was not found or could not be retrieved properly. [TOP 1 OBJECTID]‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍ Thanks Fred

FredSpataro · Answer

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

JoshuaBixby · Answer

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)) ‍‍‍‍‍

FredSpataro · Answer

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()‍‍‍‍