CF9 Error Executing Database Query

Question

I am getting this error and don't understand why:

Error Executing Database Query. [Macromedia][SQLServer JDBC Driver][SQLServer]Invalid column name 'buildno'. The error occurred in C:/data/wwwroot/webappsdev/cfeis/redbook/redbook_bio_load.cfm: line 10
    8 :    select *
    9 :   from redbook_bio
    10 :       where build_num = '#session.build_num#'
    11 : </cfquery>
    12 :
VENDORERRORCODE: 207
SQLSTATE: 42S22
SQL: select * from redbook_bio where buildno = '4700'
DATASOURCE: xxxx
******"

It is saying buildno is an invalid column name, but I do not have that name in my query. I used to, but changed both the column in the database and the column name in the query to build_num. You can see my exact code with line numbers, and that there is no 'buildno' in there. But looking at the SQL statement below that, it is still trying to use 'buildno'.

I had my editor check the directory for anywhere it says buildno and no results came back. I have restarted the CF Service and cleared the cache. Why would it still be trying to run it with buildno instead of build_num like the code says?

Most likely because you are using SELECT *. I recall something similar on HOF. The gist was the sql or table metadata(?) was being cached as well. So until you clear cache, it still reflects the old columns. — Leigh
Another little fix that I've found, is that if you go into the code, and add dbo.redbook_bio (or whatever prefix is applicable), OR, remove it if its already there, that will cause it to pull over a fresh look of the table/view. There is probably some tiny performance loss with the method you accepted as your answer. — steve

Lauren Robinson Lauren Robinson · Accepted Answer · 2014-02-27T21:21:33

There was a cfquery cache setting in the Administrator. We had it set to 100. Apparently clearing the cache template and component cache doesn't clear the cfquery cache. I changed the query name and it fixed the problem. It most likely could have been fixed by setting the cfquery cache value to 0.

CF9 Error Executing Database Query

1 Answers