multiple datasources for cfquery

Posted on 2007-10-18
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
    LVL 19

    Assisted Solution

    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 51

    Accepted Solution

    > 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

    Can you let me know all possible options with details?  

    thx much
    LVL 51

    Expert Comment


    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


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

    thx much
    LVL 51

    Expert Comment

    Yes, painless is good ;)

    Featured Post

    Looking for New Ways to Advertise?

    Engage with tech pros in our community with native advertising, as a Vendor Expert, and more.

    Join & Write a Comment

    PROBLEM: How to add your own buttons to the bottom toolbar with paging info ( result count ). While creating a cfgrid, I ran into an issue where I wanted to embed my own custom buttons where the default ones ( insert / delete / etc… ) are for aes…
    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…
    Internet Business Fax to Email Made Easy - With eFax Corporate (, you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
    This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor ( If you're looking for how to monitor bandwidth using netflow or packet s…

    745 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

    16 Experts available now in Live!

    Get 1:1 Help Now