SQLException – Too many parameters were provided in this RPC request


Say you’re updating a table using the SQL IN clause. Your SQL statement might be something like this and the number of values in listUsrPolicyID exceeds 2100:

<cfquery name="qUpdate" datasource="#request.dsn#">
   UPDATE usrPolicy
    SET reminderSent = <cfqueryparam cfsqltype="cf_sql_timestamp" value="#dateNow#">
    WHERE usrPolicyID IN (<cfqueryparam cfsqltype="cf_sql_integer" list="yes" value="#listUsrPolicyID#">)
 </cfquery>

The above query will fail and trigger the following error:

[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.

The solution then is to split listUsrPolicyID into batches containing id not exceeding 2100, then loop through all the batch lists to perform the update, i.e.

<cfscript>
variables.aUsrPolicyID = ListToArray(variables.listUsrPolicyID);
variables.cnt = 0;
variables.batchList = StructNew();

//let's create batches of 1000 user policy ID, for example
for (i=1; i lt ArrayLen(aUsrPolicyID); i += 1000) {
variables.cnt = variables.cnt + 1;
variables.batchList["#cnt#"] = "";
variables.toCnt = i + 999;
//do not go over the last available user policy id
if (variables.toCnt gt ArrayLen(aUsrPolicyID)) {
variables.toCnt = ArrayLen(aUsrPolicyID);
}
//add user policy id to each batch list
for (j=i; j lt variables.toCnt; j +=1){
variables.batchList["#cnt#"] =   ListAppend(batchList["#cnt#"],"#aUsrPolicyID[j]#");
}
} //for i
</cfscript>

<!--- there is a max of 2100 for the SQL IN clause- so let's split the list into smaller batches --->
<cfloop index="k" from="1" to="#variables.cnt#">
<cfquery name="qUpdate" datasource="#request.dsn#">
UPDATE usrPolicy
SET reminderSent = <cfqueryparam cfsqltype="cf_sql_timestamp" value="#dateNow#">
WHERE usrPolicyID IN (<cfqueryparam cfsqltype="cf_sql_integer" list="yes" value="#batchList['#k#']#">)
</cfquery>
</cfloop>

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s