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

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,
Who is Participating?
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...
How to set up and troubleshoot a linked server to an Oracle database in SQL Server


jaguar4uAuthor Commented:
Have you tried the same sql server 2008 r2, as i am unable to create instance for linked server while executing queries.....
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.