multiple datasources for cfquery

Posted on 2007-10-18
Medium Priority
Last Modified: 2012-06-27
I have a cf query that pulls out from two databases (MS SQL 2000).

select  empid, lname, fname, jobtitle, bla....
from EMPHRDB..empdemo
where jobtitle not like '%Regular%' and
          empid NOT IN (select empidsev
                                  from EMPSEVDB.empsevdemo
                                  where hirestatus not like '%term%')
order by empid, bla....

As you can see, there are two separate databases, EMPHRDB and EMPSEVDB.

In this case, what/how do you put for 'datasource' <cfquery name ="getempdemo" datasource ="????????">

thx much

Question by:ethanjohnsons
  • 3
  • 2
LVL 19

Assisted Solution

erikTsomik earned 80 total points
ID: 20101462
Here the multiple ways of doing that. You can create a SP to pull data from the second datasourse and store the data into a temp table (to make sure you have up to date data) then you can do querying from the same datasource
LVL 52

Accepted Solution

_agx_ earned 1920 total points
ID: 20101652
> As you can see, there are two separate databases, EMPHRDB and EMPSEVDB.

If the databases are on the same server and the CF datasources are configured to use a login that has permissions to both databases, you could use either datasource name in the cfquery.  


Author Comment

ID: 20101665
Can you let me know all possible options with details?  

thx much
Upgrade your Question Security!

Add Premium security features to your question to ensure its privacy or anonymity. Learn more about your ability to control Question Security today.

LVL 52

Expert Comment

ID: 20101697

We need more information about your setup
1. Are the databases on the same server?
2.  Are the CF datasources configured to use the same login? An example of a login being "sa".  
(Obviously that's just an example and you wouldn't normally want to use "sa" in a production environment)

Author Comment

ID: 20101856

YES, it works fine as you described.  It is a very simple and great solution with no pain.

thx much
LVL 52

Expert Comment

ID: 20101882
Yes, painless is good ;)

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

Question has a verified solution.

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

Hi, I will be creating today a basic tutorial on how we can create a Mail Custom Function and use it where ever we want. The main advantage about creating a custom function is that we can accommodate a range of arguments to pass to the Function and …
PROBLEM:  How to open a cfwindow or run a function on double click of a cfgrid row. One of my clients wanted to be able to double click on a row item to get more detailed information about a transaction and to be able to modify the line items i…
Loops Section Overview
Is your OST file inaccessible, Need to transfer OST file from one computer to another? Want to convert OST file to PST? If the answer to any of the above question is yes, then look no further. With the help of Stellar OST to PST Converter, you can e…
Suggested Courses
Course of the Month14 days, 3 hours left to enroll

809 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