Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

Calling Different databases with a cfquery Issues

Posted on 2010-11-30
8
320 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
Webinar: Aligning, Automating, Winning

Join Dan Russo, Senior Manager of Operations Intelligence, for an in-depth discussion on how Dealertrack, leading provider of integrated digital solutions for the automotive industry, transformed their DevOps processes to increase collaboration and move with greater velocity.

 
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

NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

Question has a verified solution.

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

This is an updated version of a post made on my blog over 3 years ago. It is unfortunately, still very relevant as we continue to see both SQLi (SQL injection) and XSS (cross site scripting) attacks hitting some of the most recognizable website and …
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

837 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