Connecting to a Oracle DB using MS SQL Opendatasource

Posted on 2005-05-10
Last Modified: 2008-01-09
I am wanting to dump some data from a Oracle DB into a SQL temp table to use in other MS SQL queries. Can this be done?
Question by:JHankinson
    LVL 8

    Expert Comment

    because you want to move data from Oracle to SQL and not the other way round maybe you should post this on the Oracle
    LVL 19

    Accepted Solution

    yes u can do this..

    2 ways..
     One is adding oracle as a linked server..
       EXEC sp_addlinkedserver
       'LONDON Mktg',

    and running ur query like
    select * into   #temptable from (select  * from (openquery([London Mktg], 'select * from my oracle table')) drv

    other way is

    select * from
          OPENrowset('MSDAORA','Oracle SID';'username';'password','my select query') drv


    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

    This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
    Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
    Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
    Via a live example, show how to shrink a transaction log file down to a reasonable size.

    746 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

    15 Experts available now in Live!

    Get 1:1 Help Now