Create a Linked Server to Oracle 10g from MS SQL SERVER 2008 R2

Posted on 2011-10-27
Last Modified: 2012-05-12
Hi Experts,

I have  task to copy data from ORACLE 10g to MS SQL Server 2008 R2 on daily basis.

I want to create a linked server on MS SQL Server for 10g and run queries to copy data accross, but unable to create linked server using MSDAORA provider.

Any suggestions?

Kind Regards,
Question by:jaguar4u
    LVL 21

    Expert Comment

    How to set up and troubleshoot a linked server to an Oracle database in SQL Server


    LVL 3

    Accepted Solution

    I have done this from SQL Server 2000 to Oracle 9i in two different ways:

    1. Using the Oracle drivers. For this you need to do a couple of things first (will find tons of documentation in the web)
    First install and configure Oracle Instant Client (this includes several steps, like adding environment variables to Windows, add path to oracle instant client, etc)
    Then, when you add the linked server, you need to provide the connection string
    This is how I have this done in my server right now:

    As you can see, you need to provide the Data Source in the form [server]:[port]/[SID]

    2. Using MS drivers. These are provided by SQL Server itself. Just search for "Microsoft Ole DB Provider for Oracle" in the list, and then do the same

    Then in the second tab you need to provide the login info for accessing the server

    If you need any other thing just let me know...

    Author Comment

    Have you tried the same sql server 2008 r2, as i am unable to create instance for linked server while executing queries.....

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Why spend so long doing email signature updates?

    Do you spend loads of your time carrying out email signature updates? Not very interesting are they? Don’t let signature updates get you down. Let Exclaimer Cloud - Signatures for Office 365 make managing email signatures a breeze.

    Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
    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.
    This tutorial will give a an overview on how to deploy remote agents in Backup Exec 2012 to new servers. Click on the Backup Exec button in the upper left corner. From here, are global settings for the application such as connecting to a remote Back…
    This tutorial will walk an individual through setting the global and backup job media overwrite and protection periods in Backup Exec 2012. Log onto the Backup Exec Central Administration Server. Examine the services. If all or most of them are stop…

    737 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

    18 Experts available now in Live!

    Get 1:1 Help Now