Create an Oracle Linked Server From SQL Server

Problem: Need to set up a linked server from SQL Server to Oracle

Solution:

Download driver – if you are installing a linked server on a 32 bit server you need the 32 bit version, if you are installing on a 64 bit server you need the 64 bit version.
Google: ole oracle 64
Google: ole oracle 64

Hard link as of 17 Jan 2013

64: http://www.oracle.com/technetwork/database/windows/downloads/index-090165.html
32: http://www.oracle.com/technetwork/database/windows/downloads/utilsoft-087491.html

I used the smallest download which had OLE DB for Oracle included which in the 64 bit case was the xcopy version which is approx 55 mb.

Install driver

Unzip downloaded file
Open up a command prompt at the unzipped directory

i used the following install command

install.bat oledb c:\oracle myhome

oledb is the component we are installing
c:\oracle is the installation path
myhome is the home name used in the registry keys

Modify Linked Server Provider

Find OraOLEDB.Oracle in the linked server provider folder
Ensure Allow inprocess is ticked

Modify Path Environment Variable

Control Panel
System
Advanced System Settings
Environment Variables
edit system variable path add the following 2 entries c:\oracle\;c:oracle\bin\

*note* the max length of an environment variable is around 2000 charecters, any longer than this and you will start to receive error messages, so you will need to trim it

restart machine

Create Linked Server

Datasource = connection string eg ip:port/service_name

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s


%d bloggers like this: