[SQL]The OLE DB provider "OraOLEDB.Oracle" for linked server ... reported an error. Access denied.

使用MSSQL建立 linked server 到 Oracle 結果執行 OPENQUERY 發生了 Access denied. 的錯誤!

今天看到了「MS-SQL 2008 在下Select 時, 無法Link DB 到Oracle?」,

於是使用MSSQL建立 linked server 到 Oracle 來試看看。

結果執行 OPENQUERY 真的發生了以下的錯誤,

Msg 7399, Level 16, State 1, Line 15
The OLE DB provider "OraOLEDB.Oracle" for linked server "oraLinkDB" reported an error. Access denied.
Msg 7350, Level 16, State 2, Line 15
Cannot get the column information from OLE DB provider "OraOLEDB.Oracle" for linked server "oraLinkDB".

 

建立方式如下,

--建立 linked server 
EXEC sp_addlinkedserver
   @server = 'oraLinkDB',
   @srvproduct = 'Oracle',
   @provider = 'OraOLEDB.Oracle',
   @datasrc = '你的oracleDB'
GO

EXEC sp_addlinkedsrvlogin 'oraLinkDB', 'False', NULL, '你的oracleUser', '你的oracleUserPassword';
go

Oracle中查到的資料如下,

image

 

然後就可以下 OPENQUERY 來查詢資料,如下,

SELECT * FROM OPENQUERY(oraLinkDB, 'select * from test');

這時欄位資訊會出來,但是資料卻不會出現,如下,

image

image

 

這時,可以設定 Linked Servers –> Providers –> OraOLEDB.Oracle 按右鍵,選取 Properties ,然後勾選 Allow inprocess 再按 OK,就可以了!

image

 

或是下SQL也是可以的,如下,

USE [master]
GO
EXEC master.dbo.sp_MSset_oledb_prop N'OraOLEDB.Oracle', N'AllowInProcess', 1
GO

設定完成後,再執行 OPENQUERY 就可以了,如下,

image

 

大家如果有其他的方式,也請讓我知道,謝謝!

 

參考資料

sp_addlinkedserver