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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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


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...

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
jaguar4uAuthor Commented:
Have you tried the same sql server 2008 r2, as i am unable to create instance for linked server while executing queries.....
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Windows Server 2008

From novice to tech pro — start learning today.