[Macromedia][SQLServer JDBC Driver][SQLServer]Could not find prepared statement with handle 1

A query issue with SET QUOTED_IDENTIFIER OFF  and a constructed WHERE

For a dealer locator. The idea was to randomize the results of the query. Users tend to pick the link at or near the top of the list returned. We want an easy to give everyone some chance at the top of the list. Looking up some code on the Internet gave me the solution of using “SET QUOTED_IDENTIFIER OFF” at the top of the query, and Order BY newid() at the end. We wanted users that may live on the border of say North and South Dakota to be able to choose both states from a list to search. This query worked great until we attempted to upgrade to ColdFusion 10. Then it brings the entire server down. Any page that runs a query will return an error similar to this:

ErrorCode 8179
Message [Macromedia][SQLServer JDBC Driver][SQLServer]Could not find prepared statement with handle 1.

Here is what I know.

This is the setup of the wherestring. I see how it could be more efficient, but it isn’t what is killing the server so let stay for now.

<cfif listLen(arguments.statelist,",") GT 1>
    <cfloop list="#arguments.statelist#" delimiters="," index="i">
      <cfset statevar = listgetat(arguments.statelist,i,",")>
      <cfif len(trim(i)) EQ 2>
      <cfif whereString NEQ "">
        <cfset whereString = whereString & " OR ibos.billSTATE = " & " '" & statevar & "'">
        <cfelse>
        <cfset whereString = " ibos.billSTATE = " & " '" &  statevar & "' ">
      </cfif>
      <cfelse>
          <cflocation url="/index.cfm"><!--- don't try and hack me bro, states should only be 2 characters. --->
      </cfif>
    </cfloop>
    <cfelse>
    <cfset statevar = left(trim(arguments.statelist),2)>
    <cfset whereString = " ibos.billSTATE = " & " '" &  statevar & "' ">
  </cfif>

This query used to work:

<cftry>    
<cfquery name="qGetIbosByStates" datasource="#request.dsn#">      
SET QUOTED_IDENTIFIER OFF
            SELECT
               ibos.agreeToTerms 
               ,ibos.billAddress1
               ,ibos.billAddress2
               ,ibos.billCity  
               ,ibos.billCompany 
               ,ibos.billEmail 
               ,ibos.billFName 
               ,ibos.billLName  
               ,ibos.billPhone  
               ,ibos.billState 
               ,ibos.billZip  
               ,ibos.cbwActive 
               ,ibos.email 
               ,ibos.homepage 
               ,ibos.homepageFolder 
               ,ibos.iboStatus 
               ,ibos.latitude  
               ,ibos.longitude  
               ,ibos.MemberID  
               ,ibos.Phone  
               ,ibos.startDate  
               ,ibos.tollfree 
               ,ibos.url  
               ,ibos.ContractType  
           FROM ibos  
           WHERE   #preservesinglequotes(whereString)#<!--- This does not play well with Quoted id and newID()'  --->
             AND (ibos.IBOstatus = 'a'  OR ibos.IBOstatus = 'i')
             AND ibos.ContractType != '30'
             AND ibos.ContractType != '32'
             AND ibos.ContractType != '18' 
             AND ibos.contractType != 'EM'
             AND ibos.contractType != '23'
            <cfif arguments.WebsiteOwners EQ "yes">AND ibos.cbwActive = '1'</cfif>
            <cfif arguments.WebsiteOwners EQ "no">AND ibos.cbwActive != '1'</cfif>
             Order BY newid()
        </cfquery>    
<cfcatch type="any">      
<cfdump var="#cfcatch#" label="Query error line516">      
<cfabort>    
</cfcatch>  
</cftry>

So to find out what the issue is I took out the multi-state option. You now can only choose one state at a time from a drop down menu.

This query does works and returns randomly ordered results.

 <cfquery name="qGetIbosByStates" datasource="#request.dsn#">
        SET QUOTED_IDENTIFIER OFF
            SELECT 
                ibos.agreeToTerms
                ,ibos.billAddress1
                ,ibos.billAddress2
                ,ibos.billCity
                ,ibos.billCompany
                ,ibos.billEmail
                ,ibos.billFName
                ,ibos.billLName
                ,ibos.billPhone
                ,ibos.billState
                ,ibos.billZip
                ,ibos.cbwActive
                ,ibos.email
                ,ibos.homepage
                ,ibos.homepageFolder
                ,ibos.iboStatus
                ,ibos.latitude
                ,ibos.longitude
                ,ibos.MemberID
                ,ibos.Phone
                ,ibos.startDate
                ,ibos.tollfree
                ,ibos.url
                ,ibos.ContractType            
            FROM ibos 
            WHERE  ibos.billSTATE = <cfqueryparam cfsqltype="cf_sql_varchar" value="#statelist#"><!--- this works with Quoted ID off and newid() ---> 
            AND (ibos.IBOstatus = 'a'
            OR ibos.IBOstatus = 'i') 
            AND ibos.ContractType != '30' 
            AND ibos.ContractType != '32' 
            AND ibos.ContractType != '18'
            AND ibos.contractType != 'EM'
            AND ibos.contractType != '23'            
             <cfif arguments.WebsiteOwners EQ "yes">AND ibos.cbwActive = '1'</cfif>
             <cfif arguments.WebsiteOwners EQ "no">AND ibos.cbwActive != '1'</cfif> 
            Order BY newid()
        </cfquery>

So what is the issue? It seems to be with “SET QUOTED_IDENTIFIER OFF” because this query works and returns random results:

 <cfquery name="qGetIbosByStates" datasource="#request.dsn#">
        <!---  SET QUOTED_IDENTIFIER OFF---><!--- commented out --->
            SELECT 
                ibos.agreeToTerms
                ,ibos.billAddress1
                ,ibos.billAddress2
                ,ibos.billCity
                ,ibos.billCompany
                ,ibos.billEmail
                ,ibos.billFName
                ,ibos.billLName
                ,ibos.billPhone
                ,ibos.billState
                ,ibos.billZip
                ,ibos.cbwActive
                ,ibos.email
                ,ibos.homepage
                ,ibos.homepageFolder
                ,ibos.iboStatus
                ,ibos.latitude
                ,ibos.longitude
                ,ibos.MemberID
                ,ibos.Phone
                ,ibos.startDate
                ,ibos.tollfree
                ,ibos.url
                ,ibos.ContractType            
            FROM ibos 
            WHERE  
            #preservesinglequotes(whereString)#<!--- This does not play well with 'Quoted id --->
            AND (ibos.IBOstatus = 'a'
            OR ibos.IBOstatus = 'i') 
            AND ibos.ContractType != '30' 
            AND ibos.ContractType != '32' 
            AND ibos.ContractType != '18'
            AND ibos.contractType != 'EM'
            AND ibos.contractType != '23'            
             <cfif arguments.WebsiteOwners EQ "yes">AND ibos.cbwActive = '1'</cfif>
             <cfif arguments.WebsiteOwners EQ "no">AND ibos.cbwActive != '1'</cfif> 
            Order BY newid()
        </cfquery>

So what does SET QUOTED_IDENTIFIER OFF do anyhow? Doesn’t seem necessary. This is one of those things where someone copies and pastes off the Internet and doesn’t really understand what they are doing.

Ok so after reading this: http://ranjithk.com/2010/01/10/understanding-set-quoted_identifier-onoff/ I just don’t think I need that at all. Someone must have been trying to do something that was solved by the use of preservesinglequotes?

So why does it bring the ColdFusion service down for the entire website? This is the first time I have been able to something like that.