My friend Si was getting this error on one of our sites:
Error Executing Database Query. [Macromedia][SQLServer JDBC Driver]Value can not be converted to requested type.
This error goes hand in hand with the error I have blogged about before "Error Occurred While Processing Request N >= N" and is, in fact, caused by the same thing. The problem is inevitably SQL SELECT statements that use (*) such as:
SELECT * FROM [table]
I am not 100% sure why this takes place, but here's my version: ColdFusion caches the SQL statement (or SQL does the caching). Then you go and you change the database structure such as adding a column. Then you run the query again. Since the actually SQL string didn't change, SQL thinks nothing has changed and tries to run the same query. However, since a column was added to the database, the cached structure not longer maps properly to the database structure, which is why it thinks it has to convert data types.
THIS IS WHY SELECT * IS NOOOO GOOD.
I can't remember off hand, but I think this is made even worse if you use CFQueryParam and SELECT *. If you spell out your query column instead of using (*), you are alllll good.
Want to use code from this post? Check out the license.