SQL query not taking values more than 2100

Question

SELECT MachineID, MachineName, count(ID) as Total, sum(Size) as TotalSize 
  FROM Files 
  join Machines on Files.MachineID = Machines.MachineID 
 Where Files.MachineID In(sql.Append(string.Format("@MachineId{0}", i));
 group by Files.MachineID,MachineName

now when the machinId count is less than 2100 the query is performed and if it machines go above 2100 an error is thrown Error:

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.

how do i make increase the limit or just avoid getting this error.. and put values in gridview thanks..

score 4 · Accepted Answer · answered Jun 15 '10 at 21:12

4

You don't say in your example where your "sql" variable comes from but if you manually build your 'IN' list (by building a string with comma delimited values in your IN statement), then all popular relational DBs have a limit to how many values you can specify in a static IN clause. The database your are using has a 2100 limit. I believe Oracle is 1000

answered Jun 15 '10 at 21:12

Mike Marshall

7,788
4
39
63

3

basically, i mean when you use a statement like 'SELECT * from myTable where ID IN (1,2,3,4,5,....)'. What I am saying is that what is inside the paranthesis after the IN keyword, is limited to 2100 or fewer comma delimited values. Your code 'sql.Append(string.Format("@MachineId{0}", i)' seems to indicate you are building a list of values in a loop. My hypothesis is that when it breaks that "sql" variable has more than 2100 comma-delimited values – Mike Marshall Jun 15 '10 at 21:18
ok.. yes that is true... i know the problem but how do i get out of it.>? like is there a way to increase the limit or put an exception(but that still wont solve it) or any other cooler way to do this... thanks for the help though.. appreciate it.. – user175084 Jun 15 '10 at 21:28
1

No, there is no way to increase the allowable size of the IN clause. You may have to look into passing arrays to your SQL or to a stored procedure, or using temp tables to stage the data before using it in your IN clause. See this thread for ideas: http://stackoverflow.com/questions/1869753/maximum-size-for-a-sql-server-query-in-clause-is-there-a-better-approach – Mike Marshall Jun 15 '10 at 21:33
hmm... thanks.. ill need to understand this now... i have never used stored procedure... but its good that i do.. any pointers before i do this?? – user175084 Jun 15 '10 at 21:47
Are you sure you need to pass in all those `machineId` s manually? No way to `join` them in or select them in another way? Has a user selected 2101+ `machineId` s? Stored routine info is on http://dev.mysql.com/doc/refman/5.0/en/stored-routines.html – MvanGeest Jun 15 '10 at 22:13

potatopeelings · Answer 2 · 2010-06-16T04:38:43.270

you could use string functions. In SQL Server your WHERE clause would be something like

...WHERE CHARINDEX(':' + <: delimited list of machine IDs> + ':', 
       ':' + CAST(Files.MachineID as VARCHAR(10)) + ':') <> 0

The : delimiters are necessary to prevent 100 from matching with 1001, 1002, 2100, etc. A sample query string would be

...WHERE CHARINDEX(':1000:1001:1002:1005:', 
       ':' + CAST(Files.MachineID as VARCHAR(10)) + ':') <> 0

SQL query not taking values more than 2100

2 Answers2