Oct 01 2013

Create Linked Server for Oracle in SQL Server 2008 R2

Category: Administration,AdministrationFatih Acar @ 15:19

If you want to fetch data from Oracle to SQL Server, you can use Linked Servers features in SQL Server Database. Linked Server is like a database link (Dblink).

Installation steps

  • Install Oracle Client on SQL Server database server
  • Restart SQL Server database service
  • Configure OraOLEDB.Oracle provider in SQL Server database
  • Create user for remote connection in Oracle database
  • Create new linked server in SQL Server database
  • Add remote login for connect Oracle database in SQL Server
  • Query Example

Install Oracle Client on SQL Server database server

You can find Oracle client application on Oracle.com. You can install Oracle client application after download. I will not tell how to install.

Restart SQL Server database service

You can restart SQL Server database service on services.msc

Configure OraOLEDB.Oracle provider in SQL Server database

After Oracle client install, you have to find Oracle provider in SQL Server database Linked Servers as OraOLEDB.Oracle

SQL Server Database

OraOLEDB.Oracle properties configure like below;

SQL Server Database

Create user for remote connection in Oracle database

SQL> create user oraconnuser identified by oraconnuser;
SQL> grant create session to oraconnuser;
SQL> grant select on table1 to oraconnuser;

Create new linked server in SQL Server database

exec sp_addlinkedserver @LinkName, @SrvProduct, @Provider, @ConnectionString

exec sp_addlinkedserver ‘ORALINK’, ‘Oracle’, ‘OraOLEDB.Oracle’, ‘OraIP:1521\DBNAME’

Add remote login for connect Oracle database in SQL Server

exec sp_addlinkedsrvlogin @LinkName, @useself, @LocalLogin, @OraUser, @OraPassword

exec sp_addlinkedsrvlogin ‘ORALINK’, ‘FALSE’, NULL, ‘oraconnuser’, ‘oraconnuser’

Query Example

select * from openquery(ORALINK,’select * from table1′)
where table1column = ‘example’

69,475 total views, 2 views today

Tags: Database Administration, MsSQL Server, Oracle, Oracle Administration, SQL Server Administration, SQL Server to Oracle DbLink