Solved

Calling Different databases with a cfquery Issues

Posted on 2010-11-30
8
317 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
Question by:myselfrandhawa
  • 4
  • 3
8 Comments
 
LVL 16

Expert Comment

by:EvilPostIt
Comment Utility
Try.....

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

Open in new window

0
 
LVL 15

Author Comment

by:myselfrandhawa
Comment Utility
have you the article by bennadel i have posted a link
0
 
LVL 52

Expert Comment

by:_agx_
Comment Utility
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
 
LVL 15

Author Comment

by:myselfrandhawa
Comment Utility
if you check the link that i have provided above of bennadel site, the same error i am getting
0
Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
LVL 52

Accepted Solution

by:
_agx_ earned 50 total points
Comment Utility
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 15

Author Comment

by:myselfrandhawa
Comment Utility
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
Comment Utility
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_
Comment Utility
If look at the entry it says ...

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

Featured Post

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
Hi, Even though I have created this Tutorial on My personal Blog, Some people might not able to find my website, So here i am posting it again Today, from the topic it is very clear that i will be showing you here the very basic usage of how we …
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
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.

772 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now