Error using TOP and * in arcpy.da.SearchCursor

[FredSpataro]

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

[FredSpataro]

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

[FredSpataro]

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

[JoshuaBixby]

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]

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