SQL Server 通過連結伺服器通路Oracle

        資料庫：SqlServer2005 、Oracle 11gR2

        系統環境：windows Server 2008R2 32位。

        最近在做項目時，需要通過sql的連結伺服器通路Oracle。以前弄過sql通路sql，感覺挺簡單的，以為通路Oracle也差不多，實際做過後才發現存在很多差距。

        步驟：

        1、SQL Server端Windows需裝對應版本的Oracle用戶端。（我安裝Oracle9i用戶端後通過PL/SQL通路Oracle11gR2，沒有問題，但是通過連結伺服器通路就無法查詢）

        2、Oracle Net Manager中建立服務名,測試連接配接通過。

        3、确認SQL Server端Windows可以通過SQLPlus通路遠端Oracle。

        4、建立link server,如下代碼。

/****** 對象:  LinkedServer [HZTMVQD]    腳本日期: 01/06/2015 11:02:50 ******/
EXEC master.dbo.sp_addlinkedserver @server = N'HZTMVQD', @srvproduct=N'Oracle', @provider=N'OraOLEDB.Oracle', @datasrc=N'172.22.75.111/VQDV3MID'
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'HZTMVQD',@useself=N'False',@locallogin=N'sa',@rmtuser=N'vqd',@rmtpassword='vqd123'

GO
EXEC master.dbo.sp_serveroption @server=N'HZTMVQD', @optname=N'collation compatible', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'HZTMVQD', @optname=N'data access', @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'HZTMVQD', @optname=N'dist', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'HZTMVQD', @optname=N'pub', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'HZTMVQD', @optname=N'rpc', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'HZTMVQD', @optname=N'rpc out', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'HZTMVQD', @optname=N'sub', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'HZTMVQD', @optname=N'connect timeout', @optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @server=N'HZTMVQD', @optname=N'collation name', @optvalue=null
GO
EXEC master.dbo.sp_serveroption @server=N'HZTMVQD', @optname=N'lazy schema validation', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'HZTMVQD', @optname=N'query timeout', @optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @server=N'HZTMVQD', @optname=N'use remote collation', @optvalue=N'true'
           

需要注意的地方：

1、資料源要寫成ip+SID 例如：172.22.75.111/VQDV3MID,否則可能無法正常查詢。也可以直接寫VQDV3MID服務名。

2、sqlserver 連結oracle可以通過兩個通路接口：“MSDAORA” 和“OraOLEDB.Oracle”。“OraOLEDB.Oracle” 通路接口是由oracle 的Oracle Probider for OLE DB 驅動提供的，必須安裝Oracle用戶端才會有。“MSDAORA”通路接口是由Microsoft OLE DB Provider for Oracle提供的，由sql資料庫自帶。

遇到的錯誤

消息

已以使用者 NT AUTHORITY\SYSTEM 的身份執行。 無法初始化連結伺服器 "hztmvqd" 的 OLE DB 通路接口 "OraOLEDB.Oracle" 的資料源對象。 [SQLSTATE 42000] (錯誤 7303)  連結伺服器"hztmvqd"的 OLE DB 通路接口 "OraOLEDB.Oracle" 傳回了消息 "ORA-01017: 使用者名/密碼無效; 登入被拒絕"。 [SQLSTATE 01000] (錯誤 7412).  該步驟失敗。

解決方案：

1、連結伺服器--HZTMVQD右鍵屬性--安全性--在本地分伺服器登入到遠端伺服器登入的映射中添加賬戶。例如：

本地登入sa，遠端使用者vqd，遠端密碼vqd123 以sa賬戶登入映射

本地登入NT AUTHORITY\SYSTEM，遠端使用者vqd，遠端密碼vqd123  以NT AUTHORITY\SYSTEM賬戶登入映射

2、添加後如果還不能通路，請在安全性--登入名裡面添加對應的賬戶，并配置設定權限

好了，暫時就總結這麼多，下次遇到新的問題在進行總結。 2015年1月13日 17:02:52 Peanuts