This bug was fixed with CF 9.0.1 released on 7/13/2010:

http://www.adobe.com/support/coldfusion/downloads_updates.html#cf9

To make this a global change:

<cffunction name="onApplicationStart">

  <cfquery name="anything" datasource="#datasource#">
   SET ANSI_WARNINGS OFF;
  </cfquery>

</cffunction>

The above will be persistant across all the SQL connections.

We are not yet sure what happens when the SQL connection recycles.  In this case, we could put the above query in onRequestStart, of course that would not be ideal.

Is there any update on this problem? Didn't see it in the hotfix.

And what's the best way to downgrade the data-direct drivers? We did a CF7 => CF9 transition and are hitting this error in several queries

Here's a very simple testcase with one table and no stored procedures.

<cfset dsn = "someSQLServerDSN">

<!--- Set up a test table --->
<cfquery name="createTestTable" datasource="#dsn#">
     CREATE TABLE test_numbers(number INTEGER, otherNumber INTEGER);
</cfquery>
<!--- Add some test data --->
<cfquery name="addTestData" datasource="#dsn#">
     INSERT INTO test_numbers VALUES (<cfqueryparam value="1" cfsqltype="cf_sql_integer">, <cfqueryparam null="true">);
</cfquery>

<!--- This is the dodgy query --->
<cfquery name="dodgyQuery" datasource="#dsn#">
     SELECT number AS dummy
     FROM test_numbers
     GROUP BY number
     HAVING COUNT(otherNumber) = 1;
</cfquery>

<cftry>
     <cfdump var="#dodgyQuery#" label="hooray">
     <cfcatch>
          <cfdump var="#cfcatch#" label="boourns">
     </cfcatch>
</cftry>

This issue has brought our planned CF9 upgrades to a screeching halt, as it's so difficult to detect until it actually happens (which is partially dependant on the data) and there's no obvious workaround.

I can reproduce this error with a much simplier example and know exactly where it breaks. First, create two simple tables:

CREATE TABLE [dbo].[Test1](
    [ID] [int] NOT NULL,
    [Name] [varchar](50) NOT NULL
) ON [PRIMARY]

CREATE TABLE [dbo].[Test2](
    [ID] [int] NOT NULL,
    [Name] [int] NOT NULL,
    [TestID] [int] NOT NULL
) ON [PRIMARY]

Then, put a single record in the first table like this:

INSERT INTO Test1 (ID, Name) VALUES (1,'Joe')

Now, create a stored proc like this:

CREATE PROCEDURE [dbo].[spwebTest] AS
SELECT  Test1.Name, Test2.Name
FROM    dbo.Test1
        LEFT OUTER JOIN dbo.Test2 ON Test1.ID = Test2.TestID
GROUP BY Test1.Name, Test2.Name
HAVING  COUNT(Test2.ID) > 0

Then, create a ColdFusion page with the following code:

<cfstoredproc procedure="spwebTest" datasource="YourDataSource" username="YourUserName" password="YourPassword">
  <cfprocresult name="qryValidateAdminLogin" resultset="1">
</cfstoredproc>

<cfdump var='#qryValidateAdminLogin#'>
<cfoutput>RC:#qryValidateAdminLogin.recordcount#</cfoutput>

The problem lies in the HAVING clause when referencing the Test2 table.  This query above won't return any records, but DOES return an empty recordset inside SQL studio manager.  ColdFusion pukes on it saying:

Variable  QRYVALIDATEADMINLOGIN is undefined

If you rewrite the query to this, it'll operate fine with no errors:

ALTER PROCEDURE [dbo].[spwebTest] AS
SELECT  Test1.Name, Test2.Name
FROM    dbo.Test1
         INNER JOIN dbo.Test2 ON Test1.ID = Test2.TestID
GROUP BY Test1.Name, Test2.Name

Definitely a bug in the driver or ColdFusion.

Here is the portion of my query that i have narrowed down where the problem is. I am still trying to reproduce with some basic tables, but from my troubleshooting it appears to be something with having a "Group By" in the sub query that is being joined to and then conditioning off of a value from that left joined sub query that contains the group by. I saw the same thing in the query in this forum post as well as the query that is having this problem that was posted in the livedocs http://help.adobe.com/en_US/ColdFusion/9.0/CFMLRef/WSc3ff6d0ea77859461172e0811cbec22c24-7fae.html

Here is my code:

<cfquery name="qTest" datasource="xxxxx" result="res">
     Select DISTINCT r.* 
From
     requisition r
          left join (Select max(oac.appID) as appID, max(oac.insertDate) as insertDate, max(oac.lastModifiedDate) as lastModifiedDate, psc.supplierOrgID 
                         FROM orgAppConfigs oac, predictSupplierConfig psc
                         Where psc.clientorgid = oac.orgid
                           and oac.appid in (11,12,13) AND oac.expirationDate > getDate() 
                         group by psc.supplierOrgID) ps ON r.mfgID = ps.supplierOrgID
Where 
     1 = 1
     AND
     (
          ps.insertDate > #getSinceDate#
          OR ps.lastModifiedDate > #getSinceDate#
     )
</cfquery>

If you I remove the conditions on the "ps" aliased table and add in another condition on the requistion table that returns 0 results I don't get the bug. As I said I am trying to come up with a replicable scenario with some simple tables. I am planning on testing out the Data Direct 4.1 Driver to see if the same problem exists.

We are currenty on CF8 and are not experiencing the problem. In regards to your performance questions with the DataDirect 3.6 vs 4.0 we did see some noticeable improvements in many of our heavier hitting queries which was a factor in the move to CF9.

Hopefully this is some good information.

-Matt

Was curious if a solution was ever found for this. We are currently upgrading to CF9 and I have also experienced this problem where the query object is undefined when the query returns 0 results. Database is also MS SQL 2005.

Just an update....

I have tried using the result attribute from the cfquery tag:

<cfquery name="test1" datasource="#dsn#" result="test1result">

A cfdump of "test1result" works fine, I get to see the execution time, record count and the sql statement.

So this is really weird, the statement is run on the db server, no exception thrown by cfquery, with cfdump "test1result" is defined BUT "test1" is not.

I also seem to be not the only person to of come across this problem, see user comments in the cf9 livedocs for cfquery: http://help.adobe.com/en_US/ColdFusion/9.0/CFMLRef/WSc3ff6d0ea77859461172e0811cbec22c24-7fae.html

Does the debugging information give any indication that the query ran?