One of my apps gets its data via lists.asmx. In a particular environment, no data was being returned and I found these entries in ULS:
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: {list-guid}, flags=0x0008000000c4108c, URL="web-url/_vti_bin/lists.asmx" Current User: {some-id} Query XML: "<Query><OrderBy><FieldRef Name="ContentType"/></OrderBy></Query>" SQL Query: "N/A"
The query cannot be completed because the number of lookup columns it contains exceeds the lookup column threshold enforced by the administrator.
Query exceeds lookup column threshold. List: {list-guid}, View: , ViewXml: <View Scope="RecursiveAll" IncludeRootFolder="True"><Query><OrderBy><FieldRef Name="ContentType" /></OrderBy></Query><ViewFields>... and a whole ton of <FieldRef> entries.
SOAP exception: Microsoft.SharePoint.SPQueryThrottledException: The query cannot be completed because the number of lookup columns it contains exceeds the lookup column threshold enforced by the administrator. ---> System.Runtime.InteropServices.COMException (0x80070093): The query cannot be completed because the number of lookup columns it contains exceeds the lookup column threshold enforced by the administrator.
I found a few references to this, but it is not the more common 5000 item List View Threshold throttling. Instead this is the List View Lookup Threshold setting of 8 (12 in SharePoint 2013).
It turns out that my app is using the default view and not specifying which fields to return in the query. In fact, there is a Level=Medium ULS log entry that has my exact query. Putting this into powershell as follows, I was able to confirm the behaviour:
$webUrl = 'some-web-url'
$listName = '{list-guid}' # note: keep the curly braces
$credential = Get-Credential -Message "Enter your login for $($webUrl)"
$uri = [uri]"$($communityRoot)/_vti_bin/lists.asmx"
$contentType = 'text/xml'
$bodyString = @"
<?xml version="1.0" encoding="utf-8"?>
<soap12:Envelope xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:soap12="http://www.w3.org/2003/05/soap-envelope">
<soap12:Body>
<GetListItems xmlns="http://schemas.microsoft.com/sharepoint/soap/">
<listName>$($listName)</listName>
<viewName></viewName>
<rowLimit>5000</rowLimit>
<query>
<Query xmlns="">
<OrderBy> <FieldRef Name="ContentType" /> </OrderBy>
</Query>
</query>
<viewFields><ViewFields xmlns="" /></viewFields>
<queryOptions>
<QueryOptions xmlns="http://schemas.microsoft.com/sharepoint/soap/">
<IncludeMandatoryColumns>FALSE</IncludeMandatoryColumns>
<IncludeAttachmentUrls>TRUE</IncludeAttachmentUrls>
<ViewAttributes Scope='RecursiveAll' IncludeRootFolder='True' />
<DateInUtc>TRUE</DateInUtc>
</QueryOptions>
</queryOptions>
</GetListItems>
</soap12:Body>
</soap12:Envelope>
"@
$result = Invoke-WebRequest -Credential $credential -Method Post -Uri $uri -Body $bodyString -ContentType $contentType
$result.RawContent
To confirm the view and the number of lookup columns, I used this powershell:
$webUrl = 'some-web-url'
$web = Get-SPWeb $webUrl
$list = $web.Lists['Shared Documents']
$fields = $list.Fields
$defaultViewXml = [xml]$list.DefaultView.GetViewXml()
Write-Output "Default View: $($list.DefaultView.Title)"
$defaultViewXml.View.ViewFields.FieldRef | Format-Table @{l="Type"; e={$fields.GetField($_.Name).TypeDisplayName}}, Name -AutoSize -Wrap
