Solved

Calling Different databases with a cfquery Issues

Posted on 2010-11-30
8
322 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
[X]
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
  • 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
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

 
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

SharePoint Admin?

Enable Your Employees To Focus On The Core With Intuitive Onscreen Guidance That is With You At The Moment of Need.

Question has a verified solution.

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

This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
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.

710 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