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

2

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.

5
  • Thank you so much @DavidBrowne and apologies for the delay in responding. As you said, I found after I had posted my request for help, that there is quite a bit of information on the internet. The solution that I found and was planning to follow, was to install teh Oracle client. Would installing the Oracle client rather than ODAC do the same job?
    – PTL_SQL
    Nov 30 '21 at 13:16
  • Not all Oracle client packages contain the OleDb provider, but using the non-Xcopy installer will work too. Nov 30 '21 at 13:22
  • Thanks again @DavidBrowne. If I want to use the ODAC download, you said I want the latest version. Is this regardless of the version of the Oracle db I'm connecting to? If the Oracle db is 11gR2 for example, do I want to download the two files for 64-bit ODAC 12.2c Release 1 (12.2.0.1.0) Xcopy for Windows x64 at the link you kindly provided above (i.e. ODP.NET_Managed_ODAC122cR1.zip & ODAC122010Xcopy_x64.zip)? Similarly, if the Oracle version is higher than 12.2c, do I still download and install the 12.2c ODAC, as it is the most recent at that link?
    – PTL_SQL
    Nov 30 '21 at 14:39
  • In general Oracle client versions work with any version of Oracle database. So you just get the latest driver. Nov 30 '21 at 14:42
  • 1
    I am most grateful @DavidBrowne, thank you so much.
    – PTL_SQL
    Nov 30 '21 at 15:14

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