Incoming Tabular Data Stream Remote Procedure Call Is Incorrect
Posted January 3, 2008 at 9:03 AM by Ben Nadel
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.
Reader Comments
Ben
If your qSelected query only returns the record id's, another option would be to nest qSelected: "id IN (select id from tablename)". There's really no reason to bring that data into ColdFusion (unless of course you need that list of id's somewhere else).
Your fix works great because you've replaced an integer list. If it was a text list and you removed the cfqueryparam tag, then you'd have to handle all the quoting yourself: "id IN ('a', 'b', 'c')"
Love the blog. Been reading it regularly for only a few weeks now. Thought I'd become part of the Comments Crew.
@Brian,
The application I was updating is over 3 years old and just was not fully thought out :) Certainly, I am sure your solution would be better. I just needed to get it fixed and uploaded in a short time (orders had to be shipped ASAP).
Thanks for enjoying the blog. Please let me know if you ever want to see any specific tutorial or demo or something.
@Brian
It's actually quite trivial to quote every element of a list :)
<cfset list = "one,two,three">
<cfset quotedList = "'#replace(list,",","','","all")#'">
@Ben
This is an interesting issue. I wonder what the technical reason for this limitation is in the driver since the database didn't have an issue with it.
@Ben
Nothing wrong with your solution, I just wanted to point out another way to fix the problem and offer some insight on what to do with a list of strings. Makes me feel all smart and stuff ;)
@Elliott
I like your 1-line quoted list fix better than mine. I used replace() like you did but I tacked the single quotes on the beginning and end with the & operator. I'm totally stealing yours.
@Elliott,
I have run into this "issue" before, which is why I immediately knew what would fix it. However, I think it is usually a different error. Usually, I *believe* that I get an "unspecified error" from the SQL server. I have never actually been shown a limit of parameters before. My guess is that the "list" attribute is doing something slightly different than the standard CFQueryParam tag.
Another method:
#ListQualify(temp, "'", ",", "ALL")#
And if it's coming from a query
#QuotedValueList(query.column)#
@Gareth
Nice. I forgot there was a listQualify()!
@Ben,
IN and especially NOT IN come with pretty big performance penalties... the longer the list the bigger the performance loss.
Unless there is a reason why you MUST use it, I'd recommend changing your query to e.g. NOT EXISTS. That can speed up query execution time considerably. I've seen improvements like 20 times faster. Yep.. not percent, times! ;-)
@Christoph,
Agreed. I have also seen this performance hit and found several ways around it (each for different types of situations). Sometimes, I have found that there is just no way around it; for example, if I have a form that has a number of list items, each with a checkbox to confirm an action, I usually just use the IN ( #FORM.lst_id# ) type methodology for the action (such as updating a column for all submitted IDs).
Yes, I could loop over the ID list and run a query each time, but then it becomes the balancing act between hitting the database 1,000 times or using an IN ( ## ) clause that has 1,000 elements. Which is better? Not sure. I think it depends on how many list elements we are talking about and I don't even have any good guidelines / suggestions.
Here is a post that I have that just covers different ways to pull data based on a list of IDs:
http://www.bennadel.com/index.cfm?dax=blog:973.view
Each is convenient and powerful at different times.
