Wednesday, May 28, 2014

Coldfusion 9 CFQuery problem

Environment:

  • Coldfusion 9.1.1.274733
  • MS SQL Server 10.50.1600


Symptoms
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
Or
Variable QRY is undefined.

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

Cause
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
Solution
In the SQL query add this line on top to suppress warning:
SET ANSI_WARNINGS OFF;
(I found this solution here: https://forums.adobe.com/thread/577811?start=0&tstart=0)

No comments: