A ColdFusion client site of mine was reporting this CFQuery error yesterday:
[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.
I have never seen this error before, but given the message, I knew exactly what it was; I had a query that was using a ColdFusion CFQueryParam with the List attribute set to true and the list data was simply too long (more than 2,100 elements). This is just a situation in which I never envisioned a use case that would have so many values - it was an order shipment module and I just never thought that they would be shipping more 2,100 items at any given time. I guess all it took was a holiday season to set them far enough back that 2,100 orders actually piled up.
To fix this, I just removed the CFQueryParam tag altogether as it wasn't really providing much of any value:
id IN ( <cfqueryparam value="#ValueList( qSelected.id )#" cfsqltype="cf_sql_integer" list="true" /> )
... just became:
id IN ( #ValueList( qSelected.id )# )
It's not a situation where I have to worry about data validation (as the data is coming out of a query), so the CFQueryParam tag was more a function of "best practices" and not because it was actually performing all that much use. Making the switch above fixed the error.
Please do not take this as any indication that the ColdFusion CFQueryParam tag has ANY draw backs. It's an awesome tag - it was just a poorly thought-out query.
Want to use code from this post? Check out the license.