This content has been marked as final. Show 11 replies
This is very strange. If you turn on de-bugging, can you see the sql?
My thoughts precisely Dan! :)
It's all at work and i'm at home now so i can't test over the weekend. The SQL that's going to the database appears to be fine though as i've picked the final query out using SQL Profiler, put it in Query Analyser and it runs fine, returning the recordset as it should....
It's all just very weird - the only thing i can think is that there's some discrepancy in the odbc drivers between CF5 and MX7, and that this query is falling over because of that. Other queries in the same page run fine :(
Yes, the odbc drivers blow chunks and really shouldn't be used with MX7.
Change the datasource to use CF's native JDBC driver.
Cheers for that technical analysis MikerRoo ;)
The dbtype attribute of cfquery doesn't have any way to explicitly select JDBC, and nor does the datasource in administrator. I'm guessing therefore that it'll use JDBC by default, which doesn't work either!
Cheers for your help anyway, time's running out on this so i'll probably just work around it with a non-working version of the site. If i do happen to figure out a solution i'll post it here...
Edit: The query runs fine on a page on its own within the same application... it just gets stranger! Must be something within the page that's throwing it off...
#DataSourceNumberTwo# ..employees e
Are the two dots there by choice or by accident?
BKBK - they're there by choice. I haven't used it before myself but it's a way to reference another database on the same sql server. That bit works fine on the separate page... :/
a way to reference another database on the same sql server
why not just db1.table1, db2.table2, etc., with one dot instead of two? Also, though you speak of referencing another database, the value evaluated seems to be a datasource, namely, DataSourceNumberTwo. Is there any confusion there?
Does it help to add the aliases f, e and a, thus, f.filt_article_code , f.filt_area_code, f.filt_topic_code, GROUP BY e.Eid, a.Article_ID?
Mainly due to the reason that db1.table1 doesn't work ;)
As far as i can see, the .. syntax is basically saying "i don't know who the database owner is but see if you can connect anyway..." the normal syntax would be db1.owner.table1. #DataSourceNumberTwo# is just a string that holds the name of the database to connect to.
I tried qualifying the article_ fields but to no avail. Cheers for your help anyway, i think i might just have to file this under 'anomalies' :(
UPDATE: I've fixed it - the query was being run within a loop, which was looping over another query from further up selecting users to query with (the SelectSubscribers query). The query had been done to select *, and it turns out that the users table had a field called SelectForSend in it as well!!
Moral of the story: Never use select * in your SQL queries!! (and probably also 'post the entire page if you post a code sample'...)
Cheers for your persistence with this BKBK - if it wasn't for testing out your suggestions i would have given up a long time ago and moved on! :D