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]
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
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]
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()
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))
Josh, thanks for verifying the issue. I'll submit to tech support.