1

From the question 'SQL IN equivalent in CAML' I learned that SharePoint 2010 has a SQL "IN" equivalent for CAML. Also that the 2007 version does not support this. The OP solved this by nesting a bunch of OR statements to achieve the same result. I tested this and the nesting indeed does the magic, but...

In my case I'm getting items from a list with around a 1000 items. I dynamically create a statement with all the IDs in nested OR-blocks for my CAML-query. I didn't worry about the big number of nested blocks as this is what MSDN states about the OR-element:

Occurrences: Minimum: 0, Maximum: Unbounded.

and:

This element can be nested inside other Or and And elements. The server supports unlimited complicated queries.

When I call the GetListItems-method from the SharePoint 2007 built-in webservice, I get following error:

Exception of type 'Microsoft.SharePoint.SoapServer.SoapServerException' was thrown. Some part of your SQL statement is nested too deeply. Rewrite the query or break it up into smaller queries.

My code is written correctly because I tested the same code with only 5 nested Or-elements and the result is as expected. My question is: what IS the limit of nested Or-elements? I cannot find this anywhere as Microsoft claims this is unlimited.

Thanks in advance!

Community
  • 1
  • 1
Abbas
  • 14,186
  • 6
  • 41
  • 72
  • 1
    Try to run the query in [CAML Query Builder](http://www.u2u.be/res/tools/camlquerybuilder.aspx). You can choose to query using Object Model or Web Services - see if you get the same problem there or there is another problem with your query. – Janis Veinbergs Feb 22 '13 at 09:53
  • Thanks for the tip but in the meanwhile I already found the solution. Thanks anyway! ;) – Abbas Feb 22 '13 at 10:23

2 Answers2

4

For those facing the same problem. I went for a search on SharePoint.StackExchange.com and found the following question:

One of the answers points to this website:

The person discovered that 500 items was too much but that a batch of 300 worked fine. So I tried this and this also works for me. So for anyone that faces the same problem, this might be the solution. :)

Community
  • 1
  • 1
Abbas
  • 14,186
  • 6
  • 41
  • 72
3

Same problem with SP2010 and nested ORs, breaks when reaching 160+ nested items.

Error raised is not very helpful (System.ArgumentException) when trying to get the SPListItemCollection = List.GetItems(Query); Looks like SQL server refuse to execute the query.

Solution is to break in multiple queries or change the way of querying.

Alberto Zaccagni
  • 30,779
  • 11
  • 72
  • 106
G2x
  • 31
  • 2