Too many parameters were provided in this RPC request. The maximum is 2100.?

Question

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.

Subplot

You're going to have to post some context. Your question conveys little or no meaning (nor does your huge subject line). – Backbreaker

wow! I never knew you could pass 2100 parameters into procedure!!!! – Rondeau

If the limit were 21 - 1 100th of this - parameters, I still would never have hit it. – Elbrus

@KM: If you're doing things right, it's a limit you should never know. :) – Chapbook

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 – Subplot

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. – Uneducated

Answer 1

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.

Chapbook

Answer 2

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.

Elsaelsbeth

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. – Subplot

Same problem bit me. Any suggestions for an elegant solution? – Barn

Nevermind! found an answer here on StackOverflow: #656667 – Barn

Answer 3

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://mcmap.net/q/41812/-correct-method-of-deleting-over-2100-rows-by-id-with-dapper
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.

Rapids

Its not that strange... an in(@p1, @p2, ..., @p2101) generated by an OR/M – Exercitation

Added link to example usage of #1 – Ungulate

Answer 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.

Elbrus

-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). – Nutritionist

@Nutritionist - 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. – Elbrus

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 – Barberabarberry

There's a warning on the IN operator documentation that people fail to read, or just plain ignore. msdn.microsoft.com/en-us/library/ms177682.aspx If you downvoted this answer you might want to reconsider. – Chanticleer