Hello Folks,
Recently I had encountered a very interesting issue when i was working with CRM Web Service and trying to fetch the data requested by user using the Advanced Find window.
The Exception which i was faced was something weird like this :
Exception: System.Data.SqlClient.SqlException: 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.
After bugging into the issue for some hours together, I found very interesting fact regarding the SQL Server. So I wanted to share this with you all.
In SQL Server, You can only have up to 2100 parameters inside IN statement in WHERE Clause.
You will get the above exception if the query similar to below is fired in SQL Server.
SELECT * FROM tablename WHERE columnname IN (1,2,3,4,5, ........ , 2101 )
Solution:
You can create new table with the values in IN statement and then JOIN your table with it. Find below example.
--Create new table
Create Table temp (myvalues uniqueidentifier)
Add all the value in IN statement of above query to the temp table above and then use this table with join to your above query as below.
-- Join new table with your table
SELECT t1.*
FROM tablename t1
JOIN temp t2 ON t1.myvalues = t2.myvalues
Hope you find this post useful. J
