Hi again experts,

I'm having a "list<string> myCustomerIDs" which contains about 3.000 entries. Though under certain circumstances it can be much more, up to 30.000 and more I guess.

I have to use this stringlist as parameter in a WHERE IN clause, like

filter.AddWithAnd(CustomerFields.CusID == myCustomerIDs);

Executing the GetMulti() method of the CustomerCollection IO receive this error message:

An exception was caught during the execution of a retrieval query: The incoming tabular data stream (TDS) remote procedure call (RPC) protocol stream is incorrect. Too many parameters were provided in this RPC request. The maximum is 2100.. Check InnerException, QueryExecuted and Parameters of this exception to examine the cause of this exception.

Obviously there are too many entries in my stringlist :-(

I realize you were discussing something similar here (http://www.llblgen.com/tinyforum/Messages.aspx?ThreadID=7567), but I didn't get if you solved it or not. By the way, I'm using Selfservice.

Does anybody know a solution to my scenario? If you need further info, please feel free to ask!

Thanks, Ingmar

Here are another discussions of the same issue: http://www.llblgen.com/TinyForum/Messages.aspx?ThreadID=4993 http://www.llblgen.com/TinyForum/Messages.aspx?ThreadID=6705

Is it possible in your situation to provide the parameters with a subquery instead of using a list? that would a way to solve your problem.

Cheers,

Hi goose,

and thanks for your help! Well, I read both articles and I can see that they address the same matter. Though I'm not sure how to use the FieldCompareSetPredicate (Subquery) in my case, as I would have to retrieve the values I'm looking for from the same table. After all I believe that this might be possible because the help file states:

compares the entity field specified with the set of values defined by the subquery elements, using the SetOperator specified. The FieldCompareSetPredicate is the predicate you'd like to use when you want to compare a field's value with a range of values retrieved from another table / view (or the same table / view) using a subquery

The help file gives the following example:

SELECT Customer.CustomerID IN (SELECT CustomerID FROM Orders WHERE Employee=2) 
// C#
filter.Add(new FieldCompareSetPredicate(
    CustomerFields.CustomerID, OrderFields.CustomerID,
    SetOperator.In, (OrderFields.EmployeeID == 2)));

But what I need would be to formulate a recursive query like

List<string> myCustomerIDs; //can have thousands of values
SELECT Customer.CustomerID IN (SELECT CustomerID FROM Customer WHERE CustomerID=myCustomerIDs)

Maybe anybody can please help me on that?

Thank you! Ingmar

Hi softwarea, could you explain a little further what you are trying to accomplish, for example, could you paste the code from where you are fetching the myCustomerIDs List?

Well, the real background is hard to explain in a few sentences. Only so much: I can NOT address another table as I would usually do in a master/detail relationship like "customers -> orders", because such a table does not exist. And for some other weird reason I must not create it dynamically.

So, please just assume that I'm simply having a list(of string) of thousands of values. The values are random, not sorted and definetely not consecutive.

I guess this was not much help, but pasting my current code here would not help much more, because as I said: I can not address another table or something like that, because there is no such possibility.

The problem here is that there is a database limitation for the number of paramaters you can pass in one call, so, I guess that if your hands are tied in the way of fetching the initial customer id's list, your only way out is doing several querys depending on amount of id's you've got on the list at that specific moment.

Cheers,

Yep, I was afraid you would say that...I was just hoping the database limitation could be solved by a configuration setting or something similar. Thank you very much anyway, goose!!!

Ups, I just saw that this thread is set to DONE. But I'm still hoping for some good idea from somebody. So, opened it again...

I'm having a "list<string> myCustomerIDs" which contains about 3.000 entries. Though under certain circumstances it can be much more, up to 30.000 and more I guess.

I have to use this stringlist as parameter in a WHERE IN clause, like Code: filter.AddWithAnd(CustomerFields.CusID == myCustomerIDs);

You can't use an IN with too many parameters, that's a database limitations, so as goose have said, "your only way out is doing several querys".

Another workaround, is to insert those IDs in a database table which you JOIN in your query.

Hi Walaa, ok, thanks again. I realize that I have to think of an alternative solution. Maybe I find a way to create a temporary table or something comparable. Thank you both so much for helping!