24

I have less than 5000 items in my library. I want to get 530 items by ids. So I am using following query.

<Where>
  <In>
    <FieldRef Name="ID" />
    <Values>
       <Value Type="Number">1</Value>
       <Value Type="Number">2</Value>
       .
       .
       .
       <Value Type="Number">530</Value>
    </Values>
  </In>
</Where>

If I use this query on list then while iterating SPListItemCollection following exception is occurred.

System.ArgumentException: Value does not fall within the expected range.
   at Microsoft.SharePoint.Library.SPRequestInternalClass.GetListItemDataWithCallback2(IListItemSqlClient pSqlClient, String bstrUrl, String bstrListName, String bstrViewName, String bstrViewXml, SAFEARRAYFLAGS fSafeArrayFlags, ISP2DSafeArrayWriter pSACallback, ISPDataCallback pPagingCallback, ISPDataCallback pPagingPrevCallback, ISPDataCallback pFilterLinkCallback, ISPDataCallback pSchemaCallback, ISPDataCallback pRowCountCallback, Boolean& pbMaximalView) 
   at Microsoft.SharePoint.Library.SPRequest.GetListItemDataWithCallback2(IListItemSqlClient pSqlClient, String bstrUrl, String bstrListName, String bstrViewName, String bstrViewXml, SAFEARRAYFLAGS fSafeArrayFlags, ISP2DSafeArrayWriter pSACallback, ISPDataCallback pPagingCallback, ISPDataCallback pPagingPrevCallback, ISPDataCallback pFilterLinkCallback, ISPDataCallback pSchemaCallback, ISPDataCallback pRowCountCallback, Boolean& pbMaximalView) 
   at Microsoft.SharePoint.SPListItemCollection.EnsureListItemsData() 
   at Microsoft.SharePoint.SPListItemCollection.GetEnumerator() 

If I rerun the query with Values from IN operator less than 500 then SPListItemCollection is not throwing an exception.

Does CAML IN operator has limit of having less than equal 500 items? Or is there any other better approach.

28

It sure looks so from your story, but isn't there a quite easy workaround for this?

<Where>
  <Or>
      <In>
        <FieldRef Name="ID" />
        <Values>
           <Value Type="Number">1</Value>
           <Value Type="Number">2</Value>
           .
           .
           .
           <Value Type="Number">499</Value>
        </Values>
      </In>
      <In>
        <FieldRef Name="ID" />
        <Values>
           <Value Type="Number">500</Value>
           <Value Type="Number">501</Value>
           .
           .
           .
           <Value Type="Number">999</Value>
        </Values>
      </In>
   </Or>
</Where>

and so on, with more nesting if over 1000 and so on.

  • Thanks Robert for quick workaround, but is it the limitation of CAML IN operator? If not then I have to recheck the issue. – Vikram Shityalkar Oct 24 '13 at 7:11
  • 1
    The In operator is pourly documented, so I am not sure – Robert Lindgren Oct 24 '13 at 8:08
  • 2
    Yes Robert, documentation is very poor and no any other blog or thread is suggesting on this. Until the Microsoft is updating the documentation let us take 500 values is limit of IN operator. +1 for your suggestion. – Vikram Shityalkar Oct 24 '13 at 10:23
  • This nesting solution works on SharePoint Online, even or large lists (over 5000 items). This works with the nest of 50 items. – ECM4D Jun 26 at 9:16
4

Can't comment due to low rep so posting as an answer, purists forgive.

Breaking a 500 element In into several nested Or tags does not work, to my despair. The limit is 500 elements hard apparently.

I have tried breaking using a limit of 500 elements, 400, 300, 200, 100, and the SPServices query failed, returning: 500 Internal Server Error with the following responseText:

Exception of type 'Microsoft.SharePoint.SoapServer.SoapServerException' was thrown.

Trying to update 500 elements at most (with a single In tag or breaking using nested Or per 400, 300, 200, 100 elements) works. Then the query returns 200 success. To my knowledge there should also be some limit of nested Or tags.

I'm using: SPServices 2014.01, jQuery 1.11, SharePoint 2013:

$().SPServices.SPUpdateMultipleListItems({
    listName: "some list display name",
    CAMLQuery: CAMLQuery,
    valuepairs: [["static name of the field", 1]],
    completefunc: function (xData) {
        if (xData.status !== 200) {
            console.log("failure");
        }
    }
});

Example query 5 * 100 + 1 that fails since there are 501 elements:

<Query>
  <Where>
    <Or>
      <In>
        <FieldRef Name='ID' LookupId='TRUE' />
        <Values>
          100 times <Value Type='Lookup'>2688</Value>
        </Values>
      </In>
      <Or>
        <In>
          <FieldRef Name='ID' LookupId='TRUE' />
          <Values>
            100 times value
          </Values>
        </In>
        <Or>
          <In>
            <FieldRef Name='ID' LookupId='TRUE' />
            <Values>
              100 times value
            </Values>
          </In>
          <Or>
            <In>
              <FieldRef Name='ID' LookupId='TRUE' />
              <Values>
                100 times value
              </Values>
            </In>
            <Or>
              <In>
                <FieldRef Name='ID' LookupId='TRUE' />
                <Values>
                  100 times value
                </Values>
              </In>
              <In>
                <FieldRef Name='ID' LookupId='TRUE' />
                <Values>
                  <Value Type='Lookup'>3089</Value>
                </Values>
              </In>
            </Or>
          </Or>
        </Or>
      </Or>
    </Or>
  </Where>
</Query>
  • Just your Syntax is wrong ;) You should not nest the Or-Tags.. use just one Or-Tag with all the In-Tags inside this. Have fun stx – user38922 Jan 22 '15 at 13:14
  • 4
    That's not right, @user38922. Or tags MUST be nested. – Rubens Farias Sep 29 '15 at 9:59
  • so is this confirmed? lots of conflicting information... – ferr Apr 6 '17 at 15:05
1

Also can't comment, but what I have done when confronted with this type of situation (yes, I have encountered this limitation), is to run the query using the min & max of ID

<Where>
    <And>
        <Geq><FieldRef Name='ID' /><Value Type='Counter'>x</Value></Geq>
        <Leq><FieldRef Name='ID' /><Value Type='Counter'>y</Value></Leq>
    </And>
</Where>

Then, assuming I have the IDs that I actually do want in some type of enumerable, use LINQ to get the items that you actually do want from the query results.

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.