24

A search query returned this error. I have a feeling its because the in clause is ginormous on a subordinant object, when I'm trying to ORM the other object.

Apparently in clauses shouldn't be built 1 parameter at a time. Thanks ibatis.

Konamiman
  • 49,681
  • 17
  • 108
  • 138
DevelopingChris
  • 39,797
  • 30
  • 87
  • 118
  • 3
    You're going to have to post some context. Your question conveys little or no meaning (nor does your huge subject line). – John Saunders Jun 22 '09 at 17:45
  • wow! I never knew you could pass 2100 parameters into procedure!!!! – KM. Jun 22 '09 at 18:03
  • If the limit were 21 - 1 100th of this - parameters, I still would never have hit it. – Mark Brittingham Jun 22 '09 at 18:15
  • 1
    @KM: If you're doing things right, it's a limit you should never know. :) – JP Alioto Jun 22 '09 at 18:15
  • for the record its not my code, its a search screen that does a secondary in clause and only throws this when there are more than 2100 main objects so its passing every id as a new parameter, query fail – DevelopingChris Jun 22 '09 at 18:43
  • 6
    Searching google for the error 'Too many parameters were provided in this RPC request. The maximum is 2100.' brings this back as the top result. I would say that validates the subject line pretty well. – PaulG Sep 15 '10 at 15:19

4 Answers4

21

Your best bet is to revise your application to pass less than 2100 parameters to the stored procedure. This is a DBMS limit that can't be raised.

JP Alioto
  • 44,864
  • 6
  • 88
  • 112
15

I got this same error when using an apparently innocent LINQ to SQL query. I just wanted to retrieve all the records whose ids were amongst the ones stored in an array:

dataContext.MyTable.Where(item => ids.Contains(item.Id)).ToArray();

It turned out that the ids array had more than 2100 items, and it seems that the DataContext adds one parameter for each item in the array in the resulting SQL query.

At the end it was a bug in my code, since the ids array had not to have so many items. But anyway it is worth to keep in mind that some extra validation is needed when using such constructs in LINQ to SQL.

Konamiman
  • 49,681
  • 17
  • 108
  • 138
  • thank you for validating the question, I would love if you upvote it, since you ran into it and it will clearly be helpful to others for it to stay in the search history of the world. – DevelopingChris Aug 27 '09 at 12:43
  • Same problem bit me. Any suggestions for an elegant solution? – Peder Rice Apr 12 '10 at 18:38
  • Nevermind! found an answer here on StackOverflow: http://stackoverflow.com/questions/656167/hitting-the-2100-parameter-limit-sql-server-when-using-contains – Peder Rice Apr 12 '10 at 18:39
9

You can do a few things:

  1. Pump the params into a temp table and use said temp table to filter your query. See https://stackoverflow.com/a/9947259/37055
  2. Create a comma-delimited array, and pass the array into SQL Server as a varchar(x). Split it out via TSQL (here are a few methods) and use the resulting rowset to filter your search results.
  3. Have a look at your application logic. It's more than a little strange to be passing 2100 parameters to a stored procedure.
Community
  • 1
  • 1
Aaron Alton
  • 22,728
  • 6
  • 34
  • 32
-4

If you are passing 2100 parameters to a single stored procedure, you are simply doing something wrong. Don't raise the limit or try to work around this abomination, figure out how to do things right.

Mark Brittingham
  • 28,545
  • 12
  • 80
  • 110
  • 5
    -1 If I have over 2100 rows to delete (By PK Id) how is that an abomination? I have a highly transactional system that pushes rows in at the rate of 400 per minute. If things go down for more than just a few minutes my purge process is going to easily have over 2100 rows that need clean up (and I can't clean up based on anything but the Id of the row). – Vaccano Mar 29 '12 at 21:44
  • 1
    @Vaccano - first, I am not fan of Linq (or other, related, ORMs). In part, it is because the attempt to shield developers from SQL leads to "abominations" like the one that surfaced here. DevelopingChris didn't *create* a sproc with that many params, his ORM did. Second, there are many ways to address a problem like his without even thinking about working through a stored procedure with 2100+ parameters. Like I said, if your mind jumps to a 2100+ parameter, dynamically constructed stored procedure as a solution, then you really need to think again. Clearly, there are better solutions. – Mark Brittingham Apr 02 '12 at 15:10
  • Commenting on an old post, but I ran into the issue as well. Using Dapper.NET to find the item ids that are inactive (~8000 of an original set of ~30000). Then passing that list into a "where id not in ()" section. It bombed at this point with the same error message. No stored procedure. And in no way does Dapper.NET shield me from the SQL. The query is there. That said, I "fixed" by pulling down all of the items, then doing a LINQ statement of .Where(c => !inactIds.Contains(c.Id)). Could I have just put the inactives statement into the where? Sure but different item class, different criteria – Robert Aug 19 '14 at 16:45
  • There's a warning on the IN operator documentation that people fail to read, or just plain ignore. https://msdn.microsoft.com/en-us/library/ms177682.aspx If you downvoted this answer you might want to reconsider. – bug-a-lot Dec 18 '15 at 15:24