Solved

Calling Different databases with a cfquery Issues

Posted on 2010-11-30
8
321 Views
Last Modified: 2012-06-27
I have one query where i am calling different tables from different cfdsn or different databases, i am getting a weird error in Coldfusion!

<cfquery datasource="#application.transaction#" name="getall">
select * from table1 where abc in
(
select * from #application.security#.dbo.table2ofdifferentdatabase
union all
select * from #application.reporting#.dbo.reportingtabledifferentdatabase
)
</cfquery>

Now i am a Coldfusion Error!



http://www.bennadel.com/blog/1146-Datasource-Names-For-All-The-Database-Tags-Within-CFTRANSACTION-Must-Be-The-Same.htm

like this but not clear on this
0
Comment
  • 4
  • 3
8 Comments
 
LVL 16

Expert Comment

by:EvilPostIt
ID: 34240338
Try.....

select * from [#application.security#].dbo.table2ofdifferentdatabase
union all
select * from [#application.reporting#].dbo.reportingtabledifferentdatabase

Open in new window

0
 
LVL 16

Author Comment

by:Gurpreet Singh Randhawa
ID: 34240381
have you the article by bennadel i have posted a link
0
 
LVL 52

Expert Comment

by:_agx_
ID: 34240409
i am getting a weird error in Coldfusion!

You've been around long enough to know we need to see the error message too ;-)
0
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 
LVL 16

Author Comment

by:Gurpreet Singh Randhawa
ID: 34240490
if you check the link that i have provided above of bennadel site, the same error i am getting
0
 
LVL 52

Accepted Solution

by:
_agx_ earned 50 total points
ID: 34240817
Do you mean this?

http://kb2.adobe.com/cps/190/tn_19023.html
"Error","jrpp-500","11/05/03","16:51:25","ApplicationName","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:\CFusionMX\wwwroot\work\querytest.cfm"

Never gotten that error myself.  
Is your application doing any of things mentioned in the entry: ie cftransaction/cfexit?
Did you try the suggested fix in technote?

Solution:
In order to avoid this issue, replace any cfexit tags in cftransaction blocks withcfabort.
0
 
LVL 16

Author Comment

by:Gurpreet Singh Randhawa
ID: 34244695
rather i am not wrapping the whole cfquery block with cftransaction, it is working on two environments, dev and test but not working on sandbox
0
 
LVL 52

Assisted Solution

by:_agx_
_agx_ earned 50 total points
ID: 34244784
Yes but is your application doing any of things mentioned in the entry: ie cftransaction/cfexit anywhere? If look at the entry it says the transaction was not in the same query throwing the error. So look for any code using cftransaction/cfexit.  Beyond that I'm out of ideas.  Like I said, I've never gotten that error.
0
 
LVL 52

Expert Comment

by:_agx_
ID: 34244787
If look at the entry it says ...

Typo correction:  If YOU look at the entry it says ...
0

Featured Post

The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

I spent nearly three days trying to figure out how incorporate OAuth in Coldfusion for the Eventful API. Hopefully, this article will allow Coldfusion Programmers to buzz through the API when they need to. Basically, what this script does is authori…
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

756 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question