[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 464
  • Last Modified:

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,
0
jaguar4u
Asked:
jaguar4u
1 Solution
 
JestersGrindCommented:
How to set up and troubleshoot a linked server to an Oracle database in SQL Server

http://support.microsoft.com/kb/280106

Greg

0
 
JohnP_RealiniCommented:
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:

http://dl.dropbox.com/u/5654637/ee/OracleOle.png

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

http://dl.dropbox.com/u/5654637/ee/MsOle.png

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...
 OracleOle.png
MsOle.png
0
 
jaguar4uAuthor Commented:
Have you tried the same sql server 2008 r2, as i am unable to create instance for linked server while executing queries.....
0

Featured Post

Creating Active Directory Users from a Text File

If your organization has a need to mass-create AD user accounts, watch this video to see how its done without the need for scripting or other unnecessary complexities.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now