SharePoint 2010 listdata.svc returns 500 Internal Server Error

I recently had a list on which all listdata.svc calls failed with a generic “500 Internal Server Error.” After reviewing some ULS logs, I found errors indicating my query attempts were aborted due to exceeding the farm’s lookup column threshold.

I frequently use the SharePoint 2010 REST interface to access list items via listdata.svc, as an alternative to handling SOAP requests via the older CSOM getlistitems. The REST interface is great for querying list data from any oData-compliant component, such as the Microsoft oData Connector for SQL Server in SSIS.

The SharePoint REST interface is based on the REST-based Open Data protocol (OData) for Web-based data services, which extends the Atom and AtomPub syndication formats to exchange XML data over HTTP.

https://docs.microsoft.com/en-us/sharepoint/dev/sp-add-ins/get-to-know-the-sharepoint-rest-service

According to TechNet documentation, the oData $select parameter in SharePoint 2010 is fully supported, and can be used to limit the number of columns returned from a list that has many columns. In theory, it should restrict the query result to the specified number of columns and combination of column types, to avoid pushing your query over the default column threshold of 8 total of any combo of lookup + person + workflow status type columns.

The Investigation

However, I recently had a list on which all listdata.svc calls failed with a generic “500 Internal Server Error.” After reviewing some ULS logs, I found errors indicating my query attempts were aborted due to exceeding the farm’s lookup column threshold.

Example with simple query, no parameters:

http://sharepointfarm/sites/testsite/_vti_bin/listdata.svc/ProductionList

w3wp.exe (0x1B2C) 0x25EC SharePoint Foundation Health 46ri High Throttled:Query exceeds lookup column threshold. List item query elapsed time: 0 milliseconds, Additional data (if available): Query HRESULT: 80070093 List internal name, flags, and URL: {REDACTED}

Example query trying to pull a single list item with ID 9001 (which exists):

http://sharepointfarm/sites/testsite/_vti_bin/listdata.svc/ProductionList(9001)

w3wp.exe (0x1B2C) 0x25EC SharePoint Foundation Health 46ri High Throttled:Query exceeds lookup column threshold. List item query elapsed time: 0 milliseconds, Additional data (if available): Query HRESULT: 80070093 List internal name, flags, and URL: {REDACTED} Current User: {REDACTED} Query XML:”9001” SQL Query: “N/A” {REDACTED CORRELATION ID}

Before requesting a targeted troubleshooting session with my farm admin (which includes bumping the logging level to verbose), I worked through some additional checks with the Microsoft Premier support engineer:

• Verified that all calculated columns on the list have valid formulas
• Verified that appropriate columns were indexed

I then tried another listdata.svc query using one of the indexed columns from the previous check.

Example query trying to filter on an indexed column, and explicitly defining the return fields via $select:

http://sharepointfarm/sites/testsite/_vti_bin/listdata.svc/ProductionList?$filter=ProductName eq 'Adobe Reader'&$select=Id,ProductRef

w3wp.exe (0x1B2C) 0x25EC SharePoint Foundation Health 46ri High Throttled:Query exceeds lookup column threshold. List item query elapsed time: 0 milliseconds, Additional data (if available): Query HRESULT: 80070093 List internal name, flags, and URL: {REDACTED} Current User: {REDACTED} Query XML:”Adobe Reader” SQL Query: “N/A” {REDACTED CORRELATION ID}

The interesting discovery in example 3 is the ULS entry following what I show above, where the REST call is translated to a CAML query equivalent, and the ViewFields section is populated with every single column from the list! The server ignores the $select parameter and builds a CAML query that tries to digest the entire list… so of course it exceeds the lookup column threshold!

Final Outcome

My Premier support ticket was eventually escalated to a SharePoint product team, only to prove what I already suspected. The select parameter does not limit the ViewFields when the REST call is translated into CAML by the server.

So if the total number of columns on your target list exceeds your farm’s lookup column threshold, there is no way to retrieve any subset of those columns using listdata.svc, outside of the large query window configured on your farm.