none
retrieving all list items via API RRS feed

  • Question

  • Is there a way in SP 2010 to get all the items of a large list via client-side API, running with a simple user permissions and workaround the resource throttling limit?

    According to this http://msdn.microsoft.com/en-us/library/ff798465.aspx article: "If you were to build a query that returns the first 100 items sorted by the ID field, the query would execute without issue because the ID column is always indexed."

    Queries that order by ID (an indexed field) and have row limit that is lower than the threshold DO NOT work for us.

    <View>

    <Query><OrderBy><FieldRef Name='ID' Ascending='False'/></OrderyBy></Query>

    <ViewFields><FieldRef Name='ID'/></ViewFields>

    <RowLimit Paged='True'>10</RowLimit>

    </View>

    All the queries we tried end up with an exception: "Unhandled Exception: Microsoft.SharePoint.Client.ServerException: The attempted
    operation is prohibited because it exceeds the list view threshold enforced by the administrator."

    I started this thread as a follow up to this thread: http://social.msdn.microsoft.com/Forums/is/sharepoint2010programming/thread/0e3558a9-2f64-427e-9622-cbf1d153516d

    Thanks for your help.

    Thursday, March 10, 2011 10:19 AM

Answers

  • After several months of support sessions with MS support, we found out that in order to execute paged queries and avoid the threshold exception the query must to contain two elements:

    1. The 'OrderBy' clause needs to contain Override='True' attribute.

    2. The scope must be 'RecursiveAll'.

     

    Here's a sample query that works:

       camlQuery.ViewXml =

                                    @"<View Scope='RecursiveAll'><Query><OrderBy Override='TRUE'><FieldRef Name='ID' Ascending='True'/></OrderBy></Query>

                            <ViewFields>

                                           <FieldRef Name='ID'/>

                            </ViewFields>

                                           <RowLimit Paged='True'>10</RowLimit>

                        </View>";

     

    I hope that helps.
    • Marked as answer by Roy Sheinfeld Tuesday, July 5, 2011 4:19 PM
    Tuesday, July 5, 2011 4:19 PM

All replies

  • You are basically trying to return too many items .. The List View Threshold was deliberately introduced to stop people trying to develop really big queries (because they are slow, inefficient, and take up too many system resources).

    The threshold level can be set from Central Administration, so you could modify it there.

    Alternatively, use the Paging features within the list to return each "paged set" within the threshold, and simply merge the resulting sets together client-side.

    http://msdn.microsoft.com/en-us/library/ee857094.aspx#SP2010ClientOM_Accessing_Large_Lists


    regards
    Martin Hatch
    MCPD SharePoint 2010 | .Net Web Development
    MCTS WSS 3.0 | MOSS 2007
    Visit my Blog - www.martinhatch.com
    Thursday, March 10, 2011 10:25 AM
  • Note -

    You will need to make sure that you are setting the following in your CAML:

    • A row limit, so that the total result set does not exceed the threshold
    • Paged="True" .. so the query knows to use the position indicated
    • a ListItemCollectionPosition .. so that the query knows where it is supposed to start / stop.

    I have tested this on a list with over 2,000,000 list items and it works fine.


    regards
    Martin Hatch
    MCPD SharePoint 2010 | .Net Web Development
    MCTS WSS 3.0 | MOSS 2007
    Visit my Blog - www.martinhatch.com
    Thursday, March 10, 2011 10:33 AM
  • As a final note, if you are still having trouble, the following 2 articles contain complete full-working code samples:

    http://blogs.msdn.com/b/ericwhite/archive/2009/11/21/using-the-sharepoint-2010-managed-client-object-model-accessing-large-lists.aspx

    http://www.itidea.nl/index.php/paging-with-client-om-and-caml/

     


    regards
    Martin Hatch
    MCPD SharePoint 2010 | .Net Web Development
    MCTS WSS 3.0 | MOSS 2007
    Visit my Blog - www.martinhatch.com
    Thursday, March 10, 2011 10:38 AM
  • final final final note ;)

    If you are doing this in JavaScript, then the same properties are still supported:

    ECMAScript SP.CamlQuery class http://msdn.microsoft.com/en-us/library/ee549731.aspx

    Full sample code of using JavaScript approach is documented here:

    http://blogs.technet.com/b/speschka/archive/2009/11/01/using-the-sharepoint-2010-client-object-model-part-3.aspx

     

    //initialize a paging instance

    ListItemCollectionPosition pos = null;

     

    while (true)

    {

    //create the CAML query

    CamlQuery cq = new CamlQuery();

     

    //set our paging position and view xml

    cq.ListItemCollectionPosition = pos;

    cq.ViewXml = "<View><ViewFields><FieldRef Name='ID'/><FieldRef

    Name='Title'/><FieldRef Name='Body'/></ViewFields>

    <RowLimit>2</RowLimit></View>";

                           

    //get items using our CAML class

    ListItemCollection lic = lst.GetItems(cq);

     

    //load the items up - note how I have to ask separate for ListItemCollectionPosition

    //if I don't, it will say property not initialized when I try and

    //work with it below

    ctx.Load(lic, itms => itms.ListItemCollectionPosition,

    itms => itms.Include(

    itm => itm["ID"],

                itm => itm["Title"],

          itm => itm.DisplayName));

     

    //execute the query

    ctx.ExecuteQuery();

     

    //get our new paging position

    pos = lic.ListItemCollectionPosition;

     

    //enumerate each item

    foreach (ListItem l in lic)

    {

    ItemsLst.Items.Add(l["Title"]);

    }

     

    //see if we've reached the end

    if (pos == null)

    break;

    else

    Debug.WriteLine(pos.PagingInfo);

    }


    regards
    Martin Hatch
    MCPD SharePoint 2010 | .Net Web Development
    MCTS WSS 3.0 | MOSS 2007
    Visit my Blog - www.martinhatch.com
    • Proposed as answer by darkashi Tuesday, July 5, 2011 5:20 PM
    Thursday, March 10, 2011 10:44 AM
  • Please pay attention to my post. I use paging, I set the row limit to 10.

    And if you'll read the previous thread, you'll see that the queries actually do not work.

    I think it works for you because you have admin rights.

    This is my code:

                List list = clientContext.Web.Lists.GetByTitle("5100 items");
                clientContext.Load(list);
                clientContext.ExecuteQuery();
                ListItemCollectionPosition itemPosition = null;
                while (true)
                {
                    CamlQuery camlQuery = new CamlQuery();
                    camlQuery.ListItemCollectionPosition = itemPosition;
                    camlQuery.ViewXml =
                      @"<View><Query><OrderBy><FieldRef Name='ID' Ascending='False'/></OrderyBy></Query>
                            <ViewFields>
                            <FieldRef Name='ID'/>
                            </ViewFields>
                            <RowLimit Paged='True'>10</RowLimit>
                        </View>";

                    ListItemCollection listItems = list.GetItems(camlQuery);
                    clientContext.Load(listItems);
                    clientContext.ExecuteQuery();
                    itemPosition = listItems.ListItemCollectionPosition;
                    foreach (ListItem listItem in listItems)
                        Console.WriteLine(" Item Title: {0}", listItem["Title"]);
                    if (itemPosition == null)
                        break;
                    Console.WriteLine(itemPosition.PagingInfo);
                    Console.WriteLine();
                } 

     

    Did you get the threshold exception, then added paging and it solved the problem?

     

    Thursday, March 10, 2011 11:07 AM
  • Erm .. I had it all in to start with and it worked fine (this was a few months ago .. so don't have active code right now).

    This should work... and other people (in your previous "duplicate" thread) said they had it working as well. There are posts all over the internet that this works.

    If you really find that it doesn't work for you then raise a support call with Microsoft to report this "bug".


    regards
    Martin Hatch
    MCPD SharePoint 2010 | .Net Web Development
    MCTS WSS 3.0 | MOSS 2007
    Visit my Blog - www.martinhatch.com
    Thursday, March 10, 2011 11:14 AM
  • I'm afraid no one said it is working. Either they didn't use a simple user credentials or suggested workarounds which are unacceptable (use admin rights, using non-working hours).

    I posted the problem here in the forum because I think it's an important issue to raise and I saw others encountered this as well.

    There's not a single post in the internet that says it works. The posts given in this and the previous threads suggested to use admin rights.

    This issue is easily reproducible. 

    I thought that there is the reason these forum exits, but I might be wrong.

     

     

    Thursday, March 10, 2011 12:06 PM
  • There is a Microsoft article on "querying large lists using the Client Object Model" which you've been pointed at from 2 separate threads now.

    Official MSDN documentation explains that large list querying IS possible using the ListItemCollectionPosition value. The fact that a Microsoft employee (as a forum moderator) has marked these responses as "Answers" only goes to prove the official stance on this.

    Like I said .. if it really isn't working then raise a call with Microsoft Support and report it as a bug.

    the lack of other internet articles on this subject perhaps also states that what you are trying to do is either not very common (in which case you are probably one of the few people to run into this particular problem) or it DOES work and there is maybe something wrong with your environment, or your install (hence .. raise a MS support call!).

    Regarding these forums, they are for the community to try and help each other out by answering questions and queries. This is not a one-stop-shop for getting official responses, an no-one here is under any obligation to help (we all do this in our own time, to try helping each other out). To be honest you creating duplicate threads, being short with your replies, accusing people of "not paying attention" and repeating your question over and over hasn't been particularly helpful either!


    regards
    Martin Hatch
    MCPD SharePoint 2010 | .Net Web Development
    MCTS WSS 3.0 | MOSS 2007
    Visit my Blog - www.martinhatch.com
    Thursday, March 10, 2011 12:14 PM
  • I'm afraid no one said it is working. Either they didn't use a simple user credentials or suggested workarounds which are unacceptable (use admin rights, using non-working hours).

    .. There's not a single post in the internet that says it works. The posts given in this and the previous threads suggested to use admin rights.

    This issue is easily reproducible.


    And to bring this to it's final conclusion (and to shut you up if nothing else ;) [jk]) I am going to create my own SP2010 instance and run some client OM code to try and duplicate this problem.

    I'll post the conclusion of that in a short while after I've generated a bunch of list data and run the code (probably 30 minutes or so)


    regards
    Martin Hatch
    MCPD SharePoint 2010 | .Net Web Development
    MCTS WSS 3.0 | MOSS 2007
    Visit my Blog - www.martinhatch.com
    Thursday, March 10, 2011 12:16 PM
  • Martin,

    I'm here to help, and I know you are too. I *really* appreciate you taking the time to take a look at this issue. 

    I only repeated the question when I tried to clarify my case.

    I opened a separate topic, because I was asked to do so by a moderator.

    I didn't "accuse" anyone - I pointed out that the suggestions you gave were already implemented.

    It is correct that official MSDN documentation explains that large list querying IS possible using the ListItemCollectionPosition value , but the documentation ignores the problem with the threshold limitation...

    I don't think there's something wrong with my environment, and I think the issue is reproducible in any environment.

    I know this is a difficult topic, so any any help will be appreciated.

    Meanwhile, I'll contact Microsoft support. If I'll know more, I'll be sure to post it in these threads.

    Thanks again for your help.

     

    Thursday, March 10, 2011 12:24 PM
  • I am going to create my own SP2010 instance and run some client OM code to try and duplicate this problem.

    I'll post the conclusion of that in a short while after I've generated a bunch of list data and run the code (probably 30 minutes or so)


    I have just created the following:

    • SharePoint Custom List (programmatically added 5100 list items)
    • Console Application (using Client OM)

    I copied EXACTLY the same code you posted above (apart from adding the "Title" Field Ref .. which you had left out) and it worked.

    I then created a NEW AD USER account, added them to the VISITORS group (read-only) and re-ran the application .. it also worked first time.

    No admin permissions .. no elevated credentials.


    regards
    Martin Hatch
    MCPD SharePoint 2010 | .Net Web Development
    MCTS WSS 3.0 | MOSS 2007
    Visit my Blog - www.martinhatch.com
    Thursday, March 10, 2011 12:43 PM
  • Note - I removed the lines:

     camlQuery.ListItemCollectionPosition = itemPosition;

    <RowLimit Paged='True'>10</RowLimit>

    and immediately got a ListView Threshold exception.

    I think we can put this one to bed now.


    regards
    Martin Hatch
    MCPD SharePoint 2010 | .Net Web Development
    MCTS WSS 3.0 | MOSS 2007
    Visit my Blog - www.martinhatch.com
    • Marked as answer by GuYuming Monday, March 21, 2011 6:22 AM
    • Unmarked as answer by Roy Sheinfeld Tuesday, July 5, 2011 4:13 PM
    Thursday, March 10, 2011 12:50 PM
  • Thanks a lot Martin!

    I suggest you try to first repro the throttling exception (by not using paging), just to make sure that adding the paging solve the exception.

    If you'll repro the exception, and adding the paging solved the exception - then it will definitely prove that something is wrong on my end (although I test with different servers and different users).

    In parallel, I'm contacting MS support.

    Thanks again.

    Thursday, March 10, 2011 12:52 PM
  • Aye done that .. I think our posts crossed over the wires
    regards
    Martin Hatch
    MCPD SharePoint 2010 | .Net Web Development
    MCTS WSS 3.0 | MOSS 2007
    Visit my Blog - www.martinhatch.com
    Thursday, March 10, 2011 1:05 PM
  • After several months of support sessions with MS support, we found out that in order to execute paged queries and avoid the threshold exception the query must to contain two elements:

    1. The 'OrderBy' clause needs to contain Override='True' attribute.

    2. The scope must be 'RecursiveAll'.

     

    Here's a sample query that works:

       camlQuery.ViewXml =

                                    @"<View Scope='RecursiveAll'><Query><OrderBy Override='TRUE'><FieldRef Name='ID' Ascending='True'/></OrderBy></Query>

                            <ViewFields>

                                           <FieldRef Name='ID'/>

                            </ViewFields>

                                           <RowLimit Paged='True'>10</RowLimit>

                        </View>";

     

    I hope that helps.
    • Marked as answer by Roy Sheinfeld Tuesday, July 5, 2011 4:19 PM
    Tuesday, July 5, 2011 4:19 PM
  • What if an OrderBy or Where clause is required on some indexed column that is not ID?

    Dave...

    Tuesday, April 8, 2014 6:10 PM