Datasource Names For All The Database Tags Within CFTRANSACTION Must Be The Same
Posted February 7, 2008 at 8:08 AM
For a long time, I had an application that was getting the following ColdFusion CFTransaction:
Data source dsn.name verification failed. The root cause was that: java.sql.SQLException: Datasource names for all the database tags within CFTRANSACTION must be the same. The specific sequence of files included or processed is: C:\...
This was an old application that has several versions running at a given time including two on the local development machine - one for internal development and one for client beta testing. As such, I assumed that the above error was being caused by DSN conflicts between the two local applications. Since our DSN values are stored in per application variables, the only thing that I could think of to explain this error was that somehow, one application was overwriting the other application's DSN mid-page. That way, somehow, a transaction might have one query with one DSN and then a second query with another DSN.
To get to the bottom of this, I poured over the code looking for ways in which ColdFusion would let this happen. The application names were totally different and file-location dependent. No possible Application name hijacking. I went over and over the queries in the CFTransaction and nothing seemed to be suspect. I couldn't figure anything out. Sadly, I never got to the bottom of this problem.
Then, yesterday, Spencer Stickland, who has since taken over the project, told me that he finally cracked the problem. After digging through numerous forums and developer resources, he came across this Adobe ColdFusion Tech Note: ColdFusion MX 6.1 and MX 7: Incorrect cftransaction error thrown. In the tech note, it explains that if you use CFExit in the middle of the CFTransaction tag there is a problem with the DSN not be released from the thread; this doesn't cause a problem with the current request, but will cause the above error to be thrown on subsequent ColdFusion requests that contain different DSN information.
I guess the lesson here is that you shouldn't put too much control logic inside of a CFTransaction tag.
Comments (5) | Post Comment | Ask Ben | Permalink | Other Searches | Print Page
Reader Comments
Great Post!
The workaround for the problem of CFTRANSACTION with different data source we have used earlier was.
Use the same datasource but prefix the table with desired database.
Following solution is not good and many times gives unexpected results. Not RECOMMENDED. I am listing it here for fun.
<CFTRANSACTION>
<cfquery datasource=firstone>
SELECT * FROM TableName
</cfquery>
<cfquery datasource=firstone>
SELECT * FROM secondone.dbo.TableName
</cfquery>
</CFTRANSACTION>
Regards,
Pinal
Posted by Pinal Dave on Feb 8, 2008 at 3:11 AM
@Pinal,
Very interesting. I have never really referred to data source name within the query value itself. Pretty clever!
Posted by Ben Nadel on Feb 8, 2008 at 7:19 AM
As to me maybe very interesting but I can understand more of it!
Posted by Gujral on Feb 8, 2008 at 3:47 PM
If I hadn't dug around in some obscure message board posts, I would have never found the problem. Seems like that Adobe KB post should have been one of the top results in Google, but oh well. :)
I'd like to test the old version out in CF8, just to make sure they resolved the error. The solution we had to go with is anything but elegant.
Posted by Spencer Strickland on Feb 9, 2008 at 4:28 PM
@Spencer,
Still glad you found it. I gotta imagine that it will work in ColdFusion 8, although maybe it wouldn't be an issue at all with the newer version.
Posted by Ben Nadel on Feb 9, 2008 at 6:22 PM
