ArcPy "Cannot open table for Load" error

Question

I have been using some slightly modified code based on socalgis.org to extract data from map services. I have had no problems until this week where I am now getting the error

RuntimeError - Cannot open table for Load

I am getting this on services that have worked fine in the past. For example the following URL works fine with no error whilst this fails with "Cannot open table for Load" error. Both are "esriGeometryPolygon" with a small number of feature on the same server.

Record extract limit: 1000
Number of target records: 403
Gathering records...
 OBJECTID >= 1 and OBJECTID <= 403
 Query: https://services.gis.ca.gov/arcgis/rest/services/Environment/Weather_stations/MapServer/2/query?where=OBJECTID >= 1 and OBJECTID <= 403&returnGeometry=true&outFields=*&f=json
Traceback (most recent call last):
 File "<ipython-input-39-454949d8d113>", line 1, in <module>
 runfile('[Path to code]', wdir='[Path to code]')
 File "C:\Users\User\AppData\Local\ESRI\conda\envs\arcgispro-py3-clone11\lib\site-packages\spyder_kernels\customize\spydercustomize.py", line 827, in runfile
 execfile(filename, namespace)
 File "C:\Users\User\AppData\Local\ESRI\conda\envs\arcgispro-py3-clone11\lib\site-packages\spyder_kernels\customize\spydercustomize.py", line 110, in execfile
 exec(compile(f.read(), filename, 'exec'), namespace)
 File "[Path to code]", line 50, in <module>
 fs[i].load(urlstring)
 File "C:\Program Files\ArcGIS\Pro\Resources\ArcPy\arcpy\arcobjects\arcobjects.py", line 421, in load
 return convertArcObjectToPythonObject(self._arc_object.Load(*gp_fixargs(args)))
RuntimeError: RecordSetObject: Cannot open table for Load

The error seems to resolve around fs[i].load(urlstring). My code below:

import arcpy
import urllib
import json
arcpy.env.overwriteOutput = True
baseURL = "[URL to map service]"
fields = "*"
outdata = "[path\to\gdb]"
# Get record extract limit
urlstring = baseURL + "?f=json"
j = urllib.request.urlopen(urlstring)
js = json.load(j)
maxrc = int(js["maxRecordCount"])
print (("Record extract limit: %s" % maxrc))
# Get object ids of features
where = "1=1"
urlstring = baseURL + "/query?where={}&returnIdsOnly=true&f=json".format(where)
j = urllib.request.urlopen(urlstring)
js = json.load(j)
idfield = js["objectIdFieldName"]
idlist = js["objectIds"]
idlist.sort()
numrec = len(idlist)
print (("Number of target records: %s" % numrec))
# Gather features
print (("Gathering records..."))
fs = dict()
for i in range(0, numrec, maxrc):
 torec = i + (maxrc - 1)
 if torec > numrec:
 torec = numrec - 1
 fromid = idlist[i]
 toid = idlist[torec]
 where = "{} >= {} and {} <= {}".format(idfield, fromid, idfield, toid)
 print ((" {}".format(where)))
 urlstring = baseURL + "/query?where={}&returnGeometry=true&outFields={}&f=json".format(where,fields)
 print ((" Query: %s" %urlstring))
 fs[i] = arcpy.FeatureSet()
 fs[i].load(urlstring)
# Save features
print (("Saving features..."))
fslist = []
for key,value in fs.items():
 fslist.append(value)
arcpy.Merge_management(fslist, outdata)
print (("Done!"))

I am using Python 3.6 and Spyder installed in a ArcGIS Pro environment.

How can I resolve this?

---

The service with the error seems to be caused by an incorrect query - the above example being generated by URLSTRING

https://services.gis.ca.gov/arcgis/rest/services/Environment/Weather_stations/MapServer/2/query?where=OBJECTID >= 1 and OBJECTID <= 403&returnGeometry=true&outFields=*&f=json

Which gives:

{"error":{"code":400,"message":"Failed to execute query.","details":[]}}

• Midavalo: Can you try a test with just the one entry from the dictionary? i.e. take out the for i... loop and just use hard references for each variable to see what happens. Basically create some test code as short as possible to help determine what the problem might be. Use lots of print() statements to print out the values passed to each tool.
• Mac Maclean: I have done that but as the error happens on multiple map service URL's from different services/servers it is not telling me anything new. It hangs each time at s[i].load(urlstring).
• Midavalo: That may be so, however it may tell potential answerers something of value. If you provide just a snippet of test code that produces the same result it may be easier to get an answer than to have users go through your full code above. See Writing code snippets to get quicker answers?
• Mac Maclean: It can be reproduced by replacing [URL to Map Service] with services.gis.ca.gov/arcgis/rest/services/Environment/… to the in the code above. I have updated question with result of above
• KHibma: Looks like its simply trying to return too much in that query. If you do OBJECTID <100 it'll return fine. Probably some big, complex polygons in there and the server is choking to return it. And it'll work if you do OBJECTID <404 and 'returnGeometry=false` (ie, everything but the geom)

Answer 1

As per @KHibma comments above I modified the maxRecordCount to a much lower number and it seems to have solved the problem

maxrc = 100 #int(js["maxRecordCount"])

I guess I need to find a way to determine the size (number of vertices) of each record and dynamically update the maxRecordCount