-2

Can someone kindly point me to a good documentation where I can get all the information I need to be able to pull data out of an oracle database from a SQL Server database please? For example, what installation, configuration, ports opening etc I need to do on the SQL Server host (SQL Server 2012) first, in order to be able to connect to the Oracle db from the SQL instance.

I will appreciate any guidance as well as link to a helpful documentation please.

Thank you so much.

1

Here's a walkthrough of creating a Linked Server to Oracle from 64bit SQL Server. There's lots of information on doing this on the web, but much of it is out-of-date. It's reproduced from my archived blog.

First, install the correct Oracle drivers. You want the latest version of the Oracle Data Access Components (ODAC), and you want the XCopy deployment. They are available here:

64-bit Oracle Data Access Components (ODAC) Downloads

One you download and unzip this into a folder run the following command from that folder:

C:\Users\dbrowne\Downloads\ODAC121010Xcopy_x64>.\install.bat oledb c:\oracle\odac64 odac64 true

Then you need to add two folders to your system path:

c:\oracle\odac64 

and

c:\oracle\odac64\bin

Then you must reboot for the system path change to be visible by services like SQL Server.

After reboot you're ready to create and test the linked server.

First configure the Oracle OleDB provider to run inside the SQL Server process, and configure it to accept parameters.

 exec master.dbo.sp_MSset_oledb_prop 'ORAOLEDB.Oracle', N'AllowInProcess', 1 

 exec master.dbo.sp_MSset_oledb_prop 'ORAOLEDB.Oracle', N'DynamicParameters', 1 

Then create the linked server definition. Instead of a TNSNames alias, use an EZConnect identifier. Here I'm specifying an IP address and a SID to connect to an Oracle Express instance running on a VM:

 exec sp_addlinkedserver N'MyOracle', 'Oracle', 'ORAOLEDB.Oracle', N'//172.16.8.119/xe', N'FetchSize=2000', ''

 exec master.dbo.sp_serveroption @server=N'MyOracle', @optname=N'rpc out', @optvalue=N'true'

 exec sp_addlinkedsrvlogin @rmtsrvname='MyOracle', @useself=N'FALSE', @rmtuser=N'system', @rmtpassword='xxxxxx' 

Now you're ready to test. We configured the linked server for 'rpc out' so we can send a simple passthrough query to test connectivity:

exec ('select 1 a from dual') at MyOracle

That's it.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.