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

The code is exactly like I posted, in its own .cfm file.

 

Weird!

 

When you say CF debug do you mean the debugging output with stack trace?

 

No, I mean when you switch CF debugging on, it will output all DB activity.  What does it show about that particular query.

How far can you simplify that query and still have the issue (ie: rip out some of the joined tables, etc).  It'd be good to get a slightly more wieldy replication case to take to Adobe.  It sounds like a bug to me.

If you can get it down to a minimum number of tables, could you post the table-create queries and queries to populate same with sample data which will create enough of a DB so as to be able to try to replicate this out of your own environment.

--

Adam

What happens if you do this?

<cfset x = QueryNew("a")>

<cfdump var = "#x#">

You understood me.  The purpose was to verify that you could actually cfdump a query.  Are there any other datasources available that you can try?  A simple query like

select count(*) x

from sometable

where 1 =3

would work.

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.

Did you try my suggestion of trying the CF8 DB drivers instead of the CF9 ones?

--

Adam

I can't answer that, sorry.  But if I was in your position, I'd probably give it a go.  Maybe just lab any new-to-CF9 DB stuff you're doing (like the Hibernate stuff).  I can't see any older CF stuff being impacted by using the previous drivers.

Obviously you should lab-test your whole app, really, as this change will hit everything.  And you might want to give some attention to performance, too.  DB driver updates usually come with performance improvements (a notable exception being the MySQL DataDirect drivers shipped with CF9...)

Also: pls raise a bug, so Adobe know to fix it, or get on to DataDirect about it.

http://cfbugs.adobe.com/cfbugreport/flexbugui/cfbugtracker/main.html

If you report back the bug reference, I'll vote for it, and... um... see if I can get it in front of the people that fix such things.

--

Adam

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.

One more discovery, using the JDBC 2.0 driver from Microsoft seemed to help resolve this issue.  I'm using the sqljdbc4.jar in my java path.

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.

The only fix that worked for us was to use Microsoft's JDBC 2.0 driver.  These instructions for getting that setup are rather old, but still apply.  We're using Windows 2008 R2 IIS 7.5, ColdFusion 9 Enterprise, with SQL 2005 and it works great with this driver.

http://kb2.adobe.com/cps/186/tn_18652.html

To get the driver, just Google the terms [microsoft sql jdbc driver] and you'll get links for the documentation and download of the driver.

One reason you may be getting this error is that when results are grouped using GROUP BY you may get warnings like:

Warning: Null value is eliminated by an aggregate or other SET operation.

In that case the new driver seems to pick this warning up and does not create the query object. A workaround would be to use

SET ANSI_WARNINGS OFF

before the query itself.

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.

We had the exact same problem with full text searching using the built-in SQL Server driver in ColdFusion. The problem lies, as you said, in the actual search string. SQL Server has certain words called 'noise words' that it doesn't like to match with in full text searches. It will always complain about these noise words with this message: "Informational: The full-text search condition contained noise word(s)." You can see if you are getting this message by running the query in  SQL Server Management Studio and checking the Message tab. As stated above, these warning messages will cause the query to return as undefined in ColdFusion, even though the query will run perfectly fine in SQL Server.

We solved this, for the time being, by creating a second datasource in ColdFusion that uses ODBC to connect to SQL Server instead of the default built-in driver. The ODBC driver does not have the problem of returning an undefined query when warning messages are given by SQL Server. This isn't the best solution, but so far running our full text searches under this secondary datasource is the only thing we have found to work 100% of the time. You could also strip out all the noise words, unless you need 'and' or single characters/numbers to be in your search.

You can read more about noise words here:

http://retrowebdev.blogspot.com/2006/09/removing-sql-server-full-text- noise.html

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

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

Yes, it was fixed in 9.0.1. The change list reports that bugs 80384 and 81153 were fixed with this release. My testing also confirmed this to be true. If you're still having problems, please verify that you've correctly installed the update. If the problem persists maybe you've found a new bug.

OK so the above proc had a "SET NOCOUNT ON" statement in it.  When removed it works.  Still seems odd as it worked in all other versions of CF.