38

I do experiment with LINQ since some time. Typical method to enumerate through a collection and change some of its properties in my code would look like:

ATDataContext dc = new ATDataContext(Settings.connection_string);

int[] col = ListViewClass.getListViewSelectedPositionTags(listView);

try
{
    foreach (var item in col)
    {
        var ctx = (from r in dc.MailingLists
                   where r.ID == item
                   select r).Single();

        ctx.Excluded = 'Y';
        ctx.ExcludedComments = reason;
    }

    dc.SubmitChanges();
}

Later on I have got an advice to do this by... seems like much smarter way:

var ctx = from r in dc.MailingLists
    where col.Contains(r.ID)
    select r;

foreach (var item in ctx)
{
    item.Excluded = 'Y';
    item.ExcludedComments = reason;
}

dc.SubmitChanges();

Iit makes sense on so many levels and I love this solution. It’s smart and faster than the first one.

I have used this solution in a production environment for some time.

What was my surprise after few weeks when searching an application log files and see this:

"The incoming tabular data stream (TDS) remote procedure call (RPC) protocol stream is incorrect. Too many parameters were provided in this RCP request. The maximum is 2100."

The LINQ to SQL converts where col.Contains(r.ID) to INclause looking something like:
WHERE ID IN (@p1, @p1, @p2 … )

The col collection reached (in my case) more than 2100 elements and the query failed to perform. I have done some research on the problem and what I ended up is:

“… Maximum number of parameters in the sql query is 2100. There is more limitations, like the fact that the whole query string cannot be longer than 8044 characters.”

I have loved the second solution so much. I am so disappointed with these hard-coded limitations of the SQL Server.

Did I miss something? Is there anything I can do to be able to use the where col.Contains(r.ID) version?

Stephen Kennedy
  • 20,585
  • 22
  • 95
  • 108
Mariusz
  • 908
  • 1
  • 13
  • 28

1 Answers1

57

The limits are hard-coded:

  • Parameters per stored procedure 2,100
  • Parameters per user-defined function 2,100

I wrote some code before that split the Contains query into batches and combined the results... see here for more.

Community
  • 1
  • 1
Marc Gravell
  • 1,026,079
  • 266
  • 2,566
  • 2,900
  • 8
    turns out the error message/documentation is wrong. They must have a check that goes `>= 2100` rather than > 2100 because the actual limit is 2099 just observed this with trying to send 4 parameter batch, which gives an even size of 525 – JonnyRaa Apr 20 '15 at 12:49
  • 2
    @JonnyLeeds interesting; I wonder if it does something like always include space for a return value (even if not used), which is implemented kinda as a parameter – Marc Gravell Apr 20 '15 at 14:09
  • 1
    yeah possibly. The documentation there only specifically mentions functions and stored procedures, whereas I was just running a big set of inserts, however the exceptions from running `command.Execute[Blah]` (in c#) also mention 2100 – JonnyRaa Apr 20 '15 at 14:13
  • 1
    When C# calls SQL Server without a stored proc, it uses `sp_executesql`. This means you're using one parameter for the SQL statement itself. – Jonathan Allen Nov 09 '19 at 05:22
  • 1
    @Jonathan if that wer the case, it would be using 2 - one for the SQL and one for the params definition parameter; I didn't think it actually issued it that way, though, at the TDS level - but I could be wrong, it has been a while since I dabbled with raw TDS – Marc Gravell Nov 10 '19 at 21:50
  • 1
    I can't say you're wrong, all I know for certain is that my C# code can't use the full 2100 parameters. – Jonathan Allen Nov 11 '19 at 16:28