I always forget about this until I start working with ColdFusion and MSSQL again. You're plugging along, and suddenly a page that was previously working starts throwing an error:
Error Executing Database Query.
[Macromedia][SQLServer JDBC Driver]Unsupported data conversion.
Relax, your code is (likely) fine. This typically happens when you add a column to your table and use "SELECT *" in the query. Somewhere along the line the list of columns is cached and since the list of columns you're selecting in the query hasn't changed ColdFusion chokes when what it receives doesn't match what it expects.
The fix couldn't be simpler – just toss a semi-colon at the end of your CFQUERY and watch your problems go away. If you make another change just remove the semi-colon.
So a query that looks like this:
<CFQUERY NAME="qryUsers" DATASOURCE="dsn"> SELECT * FROM users </CFQUERY>
Becomes:
<CFQUERY NAME="qryUsers" DATASOURCE="dsn"> SELECT * FROM users; </CFQUERY>
I fully expect that I'll be stumbling across this page in 6 months.
Tags: ColdFusion, Development, MSSQL
March 26th, 2009 at 6:01 am
Ah nice! Yeah I run into this all the time and always assumed the caching somewhere and have always resolved it by restarting my cf service. Typing a semicolon is a it faster isn't it?
April 23rd, 2009 at 6:39 am
The solution is to put all the columns you want in the column list instead of doing a *.
It's bad practise to throw in a * anyways and can produce unexpected results in your code.
June 30th, 2009 at 5:24 am
Thanks a lot! You save my time.
October 8th, 2010 at 10:46 am
Bless you for posting this. You saved me a ton of time.
October 27th, 2010 at 8:33 am
I don't know if I agree that this is caching though… if it were, wouldn't you be able to remove the semicolon after the first execution of the query?
It seems like ANY change to the SQL alleviates the error, though I'm not sure that caching is the cause for sure. This isn't just for "select *" queries, I just had it happen to me on a query wherein I was grabbing one specific column from my query.
I found that reversing the order of the items in the where clause also helped.
October 27th, 2010 at 8:48 am
Mike,
if you change the data type of one of the columns maybe.
This is definatley cfquery caching.
If you remove the ; it will use the old cached version so that might be what you're experiencing.
Rule of thumb:
Use and explicit column selection.
October 27th, 2010 at 9:00 am
I did use explicit column selection.
How is this cache cleared? Is it necessary to bounce the coldfusion process for this to reset?