"[Macromedia][SQLServer JDBC Driver][SQLServer]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. "
Now we will reproduce the error with some sample ColdFusion code and the solution for the above error.
<cfquery name="testQuery" datasource="SQL_Test">
SELECT
Employee_ID
FROM
employee
</cfquery>
<cfset request.employeeIDList = valueList(testQuery.Employee_ID) />
<cfquery name="testQuery2" datasource="SQL_Test">
SELECT
Employee_ID
FROM
employee
WHERE
Employee_ID IN (<cfqueryparam cfsqltype="cf_sql_integer" value="#request.employeeIDList#" list="true" />)
</cfquery>
The actual error lies in the query "testQuery2" of highlighted section. The cause of the error is, from the first query "testQuery" we are getting no of records more than 2100 but the ColdFusion cfqueryparam can process maximum 2100 no of list elements.
If the no of record we are getting in the first query is more than 2100 then only we will get this error and this is specific for MS SQL server with cfqueryparam. I tested the same with My SQL but I did not get any error for that.
The solution for the above error is:
Remove cfqueryparam from the second query.
e.g:
<cfquery name="testQuery2" datasource="SQL_Test">
SELECT
Employee_ID
FROM
employee
WHERE
Employee_ID IN (#request.employeeIDList#)
</cfquery>
This is a very misleading "solution" to the 2100 limit problem. By removing the CFQUERYPARAM tag you are opening yourself up to a host of SQL injection problems.
ReplyDeleteWhy not pass in the list as a single parameter (ie. remove the list="true") and then let SQL split up the list itself with a UDF.
duo_chunk_split_me() @ http://www.sql-server-performance.com/forum/threads/table-design-advice-needed.25585/