Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Pulling data form oracle and sql in single query

Pulling data form oracle and sql in single query

Pulling data form oracle and sql in single query

I have 2 different DBs one in orcale and other one in SQl that I need to pull data from...
I need 3 output columns: Employee Name   Date Hire   Salary     
The problem is that Date Hire is in Oracle and salary is in SQL and Employee Name is in Both DBS.
SQL query:
<cfquery datasource="SQL" username="USER" password="PWD" name="SQLQuery">
            select EmployeeName, Salary
            from   Employee
Oracle query:
<cfquery datasource="ORA" username="USER1" password="PWD1" name="OracleQuery">
            select EmpName, Date_Hired
            from   EmpORA

Is there a way to make it work with a query... and the Date Column has time stamp with date (2009-05-27 11:01:57.0). How do I get rid of the time stamp...

Yes I do have unique Employee ID in both the tables...
As I stated above, my connection source is Cold Fusion...
For each query I have to provide DataSource, UID, PWD to pull the data from servers...

Can you examplify how can I do a join on Employee ID, include connection strings from both Data sources using Openrowset

I tried to do the following:
<cfquery datasource="SQL" username="USER" password="PWD" name="SQLORAQuery">
            select EmpName, Date_Hired
            from   openrowset('MSDAORA.1', 'User ID=USER1'; 'Password=PWD1'; 'datasource=ORA', EmpORA)
            where ORAEMPID == SQLEMPID

Is there anything wrong with my query that it is not working...

RE: Pulling data form oracle and sql in single query

I don't speak Cold Fusion, so I can't be sure, but it doesn't sound like something you'd be able to do. A question asked on forum232: Adobe(Macromedia): ColdFusion would get you a more authoratative answer, and maybe the one you want to hear.

Is it possible to establish a database link from one database to the other? Then you could create views on (say) the Oracle database that would point at the tables on the SQL database. Then your cf script would only have one database to connect to.

-- Chris Hunt
Webmaster & Tragedian
Extra Connections Ltd

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close