0

I'm using the following CAML query in my CSOM code to try to determine the highest list ID in a list. I'm getting the standard list threshold limit error (because the list contains over 23000 items).

<View Scope="RecursiveAll">
  <Query>
    <OrderBy>
      <FieldRef Name="ID" Ascending="False" />
    </OrderBy>
  </Query>
  <RowLimit>1</RowLimit>
</View>

My understanding from the other question is that the ID field is already indexed by SharePoint automatically.

The query works during the Happy Hour that we setup for our farm.

Can anybody help me figure out why this isn't working for non-Happy Hour queries?

Update based on comments: This is on-premise SharePoint 2013. Happy Hour has the threshold set to unlimited. During the rest of the time we have the default 5000 item limit.

Furthermore, in the same webapp, there is another site collection that has a list with 27000+ items. The same CAML query above works irregardless of Happy Hour.\

Edit 2: I also tried this CAML without success during non-Happy Hour times:

<View Scope="RecursiveAll">
  <Query>
    <OrderBy>
      <FieldRef Name="ID" Ascending="FALSE" />
    </OrderBy>
  </Query>
  <ViewFields>
    <FieldRef Name="ID" />
  </ViewFields>
  <RowLimit>1</RowLimit>
</View>

On a whim, I also tried:

<View>
  <Query>
    <OrderBy>
      <FieldRef Name="ID" Ascending="FALSE" />
    </OrderBy>
  </Query>
  <ViewFields>
    <FieldRef Name="ID" />
  </ViewFields>
  <RowLimit>1</RowLimit>
</View>

and

<View>
  <Query>
    <Where>
      <Gt>
         <FieldRef Name="ID" />
         <Value Type="Integer">0</Value>
      </Gt>
    </Where>
    <OrderBy>
      <FieldRef Name="ID" Ascending="FALSE" />
    </OrderBy>
  </Query>
  <ViewFields>
    <FieldRef Name="ID" />
  </ViewFields>
  <RowLimit>1</RowLimit>
</View>

All work during Happy Hour, but not during non-Happy Hour.

Here are the "columns" in the list that is failing:

Title                Single line of text    
Market               Choice 
Vendor               Choice 
Year                 Choice 
Period               Choice 
Category             Choice 
Status               Choice 
Report Type          Choice 
Assigned To          Person or Group    
History              Multiple lines of text 
Created              Date and Time  
Modified             Date and Time  
Created By           Person or Group    
Modified By          Person or Group    
Checked Out To       Person or Group

I can't reveal the columns of the other lists which are succeeding, but it the same as above plus an addition 10 more, most of which are also Choice and Person or Group type fields.

  • What is threshold value you set for Happy hours and what is default threshold value? – Siddharth Vaghasia Jun 4 at 14:05
2

Edit- Looking at updated details in question, I suspect your list have more than 8 look up columns. And in query we are not passing viewfields and hence it is trying to return all fields. Can you pass viewfield tag with ID column and see if it works.

I just tried below code and used your CAML query for a List which has 115080 items and threshold is set to 20000. I got expected Item's ID. Please refer below. You assumptions is correct is should return data irrespective of happy hours....

enter image description here

function retrieveListItems() {

    var clientContext = new SP.ClientContext(_spPageContextInfo.siteAbsoluteUrl);
    var oList = clientContext.get_web().get_lists().getByTitle('MYList');

    var camlQuery = new SP.CamlQuery();
    camlQuery.set_viewXml('<View Scope="RecursiveAll"><Query><OrderBy><FieldRef Name="ID" Ascending="False" /></OrderBy></Query><RowLimit>1</RowLimit></View>');
    this.collListItem = oList.getItems(camlQuery);

    clientContext.load(collListItem);

    clientContext.executeQueryAsync(Function.createDelegate(this, this.onQuerySucceeded), Function.createDelegate(this, this.onQueryFailed));        

}

function onQuerySucceeded(sender, args) {

    var listItemInfo = '';

    var listItemEnumerator = collListItem.getEnumerator();

    while (listItemEnumerator.moveNext()) {
        var oListItem = listItemEnumerator.get_current();
        listItemInfo += '\nID: ' + oListItem.get_id();


    }

    alert(listItemInfo.toString());
}

function onQueryFailed(sender, args) {

    alert('Request failed. ' + args.get_message() + '\n' + args.get_stackTrace());
}

Below is response I got, last item id shown in alert.

enter image description here

Hope this helps...Happy coding..!!!

  • Yes, I attempted adding a FieldRef for just the ID column, as well as removing the recursive scope. Reading some of the other suggested questions/answers here on SO, I also tried adding a query for ID > 0 which seemed to help some people. I'm beginning to suspect this is related to the default view on the lists. I would have assumed that setting ViewXml would override the lists' view. But then there is that old adage about assumptions. – Ants Jun 5 at 12:21

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.