Too many parameters were provided in this RPC request. The maximum is 2100.

I was working in some data base related stuff I got the following error message:

"[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 was using MS SQL Server 2008.
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>

Data Base Performance Enhancement By cfqueryparam

Data Base Performance Enhancement By cfqueryparam

In ColdFusion, generally we use cfqueryparam for security causes. But cfqueryparam also helps in database query performance enhancement. Let see how this works.

I wrote the following code in ColdFusion then run the code.

<cfset request.param = "test" />

<cfquery name="TestQUery" datasource="LOCAL_DB">

    SELECT * FROM RETS_tblField WHERE vcField_Long_Name = '#request.param#'

</cfquery>

Again I changed the request.param = “test1” and run the code. When I checked the database cache I got the following out put.

There is two cache entries for the two times execution of the same query. If our application will run this query 1lakh times then it will create 1lakh entry in the cache.

Which will create the following issue.

• There will more cache entries for a single query.
• Frequency of the auto cache deletion of the database will increase.
• It will use more RAM size as all the cache entries will be load into RAM for searching all the queries.
• The same query will compile as many times it runs.

If we write our query in following way.

<cfset request.param = "test" />

<cfquery name="TestQUery" datasource="LOCAL_DB">

    SELECT * FROM RETS_tblField WHERE vcField_Long_Name = <cfqueryparam cfsqltype="cf_sql_varchar" value="#request.param#" />

</cfquery>

Also I changed the request.param="test1" and run it twice. Then I got following out put.

Here it creates only one cache entry for the same query also compiles only once. It doesn't depend on no of times you runs the query.

The most important thing is how we will check the cache entries of database.

For MS-SQL:

To Get Cache Entries

SELECT

cp.objtype,st.text

FROM

sys.dm_exec_cached_plans cp

CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) st

WHERE

st.text NOT LIKE '%sys.dm_exec_cached_plans%'

AND

st.text LIKE '% RETS_tblField %'

NOTE: Here RETS_tblField is the table name . So the above query will list out cache entries which contains RETS_tblField as a part of text.

To Clear Cache Entries

DBCC FREEPROCCACHE

Similarly you can find queries to check database cache entries for other database.

Insert Null Values into Data Base using cfqueryparam

It is always a good practice to use cfqueryparamparam while passing parameter for any data base operation in ColdFusion. It helps us SQL injection attack.

During insert and update operation of data base sometimes we need to save null values into the data base and these null value insertion decision is made in run time.

Like if var x = 0 then insert null value to column P else insert some other value.

So now the point is that how to insert null using cfqueryparam???

The cfqueryparam has a attribute called "null",if we make null="yes/true" then null value will be entered into the DB without looking for the value in the "value" attribute. But the question is that how make that null attribute field dynamic, so that when a input field or query returns empty string it will fill that field as null in data base.

For to make it dynamic we can use a function called YesNoFormat(arg) .Which returns "No" for following values

1.If arg=0

2.If arg=""

3.If arg="false"

4.If arg=false

Anything else will return Yes. So by using any expression within that function we can enter null value as per our requirement.The example is given as follows.




The seasonId value will be the value of the fetched data, if it exists else it will take null value. Because qry_getSeasonID.recordCount gives zero(0) when no record exists and the boolean not operation gives true so the yesNoFormat() returns yes .Means the null value will be entered into the particular field.