Wednesday, May 28, 2014

Coldfusion 9 CFQuery problem


  • Coldfusion
  • MS SQL Server 10.50.1600

Here is a really weired and rare problem. With a CFQuery tag like this:
<cfquery datasource="..." dbtype="ODBC" name="local.qry" result="local.result"></cfquery>
I'm getting error message like these:
Element QRY is undefined in LOCAL
Variable QRY is undefined.

After some digging, I found the cause, and subsequently an interesting solution.

I ran the query in SQL Server Management Studio. The "Results" grid is showing no rows. The real interesting thing is that the "Messages" panel actually has a warning which took me a while to notice:
Warning: Null value is eliminated by an aggregate or other SET operation.

So if all the following conditions are met, my query variable will became undefined, instead of an cfquery object with no row.
  • Result is empty
  • A warning is shown when run the query in SQL Management Studio
In the SQL query add this line on top to suppress warning:
(I found this solution here:

No comments: