Cfquery: Could not find prepared statement with descriptor x

Question

We periodically get the following error, but often:

Error Executing Database Query. [Macromedia][SQLServer JDBC Driver][SQLServer]Could not find prepared statement with handle 1.

I implemented two solutions to this problem that I could find -

• Put a semicolon at the end of the query (supposedly to force recompile the query)
• Put this MS-SQL at the end of the query: OPTION (RECOMPILE)

I applied fix 1 and the errors were stopped for the rest of this day. The next day, the error returned.

I applied Patch 2, and the same thing happened - more errors until the next day.

I understand that whenever I use <cfqueryparam> , the prepared statement is used automatically, but there is no way to get db to "rework" every time a query is used, rather than relying on cached expressions?

Please note that I do not have administrator access to this Cf server.

Cf Version - 9.0.0

SQL Server 9.0.3054.

Offensive request:

 <cfquery datasource="#dsn#" name="q" maxrows="1"> SELECT ID FROM tableOne WHERE ID = <cfqueryparam cfsqltype="cf_sql_integer" value="#url.ID#"> ORDER BY ID </cfquery> 

thanks

Answer 1

You can set the CachedWithin attribute in your cfquery statement ..

 <cfquery name="GetParks" datasource="#dsn#" name="q" maxrows="1" cachedwithin="#CreateTimeSpan(0, 0, 0, 0)#"> 

the above example will always store the request from the cache

Answer 2

regarding your error: I saw this error before when the value of # dsn # changed from one request to the next. If this is not the case, try applying your CF updates since you are still at 9.0.0.

Regarding your question: Set “Max. Consolidated reports” to 0. This will require the CF to re-prepare the application each time. In addition, unchecking the “Keeping in touch” checkbox will also require re-compilation, since the database connection is re-negotiated for each request.

Also a technical note: SQL Server will always cache the execution plan no matter what you do in CF. CF just holds onto the handle for this prepared statement, so it doesn't need to pass SQL through the wire every time.

Answer 3

This may sound silly, but do you think that perhaps the maxrows attribute might cause your pain. Try using TOP and see what happens.

 <cfquery datasource="#dsn#" name="q"> SELECT TOP 1 ID FROM tableOne WHERE ID = <cfqueryparam cfsqltype="cf_sql_integer" value="#url.ID#"> ORDER BY ID </cfquery>