SQL Server Linked Server To Oracle

SQL Server Linked Server To Oracle

1.Install Oracle 10g Release 2 Client
and Oracle 10g Release 2 ODAC 10.2.0.2.21
on the server that is running Microsoft SQL Server

2.Create an alias name on the server
that is running SQL Server that points to an Oracle database instance.
(tnsname.ora)

3.Enable Allow inprocess on Oracle Provider for OLE DB
SQL Server Instance
->Server Objects -> Linked Servers
->Providers->OraOLEDB.Oracle->Properties
checked Allow inprocess

4.Execute sp_addlinkedserver to create the linked server,
specifying OraOLEDB.Oracle as provider_name,
and the alias for the Oracle database as data_source.
The following example assumes that the alias has been defined as DQORA8:
exec sp_addlinkedserver @server='OrclDB',
@srvproduct='Oracle',
@provider='OraOLEDB.Oracle',
@datasrc='DQORA8'

5.Use sp_addlinkedsrvlogin to create login mappings
from SQL Server logins to Oracle logins.

EXEC sp_addlinkedsrvlogin @rmtsrvname = 'OrclDB',
@useself = 'false',
@locallogin = 'Joe',
@rmtuser = 'OrclUsr',
@rmtpassword = 'OrclPwd'

6.若要寫入資料則要而外設定RPC,RPC OUT
USE master;
EXEC sp_serveroption 'OrclDB', 'rpc out', 'True';

7.限制
Table Name 使用大寫或小寫
每個欄位值都必須提供無論是否可NULL或有預設值
日期型別需使用變數來寫入

例如:
DECLARE @v1 datetime SET @v1 = CONVERT(datetime,'14-sep-94')
EXEC('INSERT INTO
DYNORCL.SALES(ID, ORD_NO, ORD_DATE, QTY)
VALUES (?, ?, ?, ?)', '6380', '6871', @v1, 5) AT OrclDB

一些舊版的Oracle Provider可能不支援AVG()函數(Oracle 10g已直接支援AVG),
若發生錯誤可使用OPENQUERY來達成同樣效果
例如:
SELECT AVG(QTY) FROM ORA..RPUBS.SALES;
發生錯誤則改用
SELECT * FROM OPENQUERY
(
ORA
,'SELECT CAST (AVG(QTY) AS numeric) FROM ORA..RPUBS.SALES'
);


[Reference]
SQL Server 2008 Books Online (August 2009)
Oracle Provider for OLE DB
http://msdn.microsoft.com/en-us/library/ms190618.aspx