在SQL Server中使用OPENROWSET訪問ORACLE數(shù)據(jù)庫時(shí)，你可能會(huì)遇到各種坑，下面一一梳理一下你會(huì)遇到的一些坑。

?

1：數(shù)據(jù)庫沒有開啟"Ad Hoc Distributed Queries"選項(xiàng)，那么你就會(huì)遇到下面坑。

?

SELECT?TOP 10 * FROM?OPENROWSET('OraOLEDB.Oracle', 'ESCM_134';'test';'test', 'SELECT * FROM TEST.MY_SET')

Msg 15281, Level 16, State 1, Line 1

SQL Server blocked access to STATEMENT 'OpenRowset/OpenDatasource' of component 'Ad Hoc Distributed Queries' because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of 'Ad Hoc Distributed Queries' by using sp_configure. For more information about enabling 'Ad Hoc Distributed Queries', search for 'Ad Hoc Distributed Queries' in SQL Server Books Online.

?

?

出現(xiàn)這個(gè)錯(cuò)誤，只需要開啟數(shù)據(jù)庫"Ad Hoc Distributed Queries"選項(xiàng)即可。如下所示

sp_configure? 'show advanced option',1;GORECONFIGUREsp_configure 'Ad Hoc Distributed Queries',1;GORECONFIGURE

?

2：遇到“The OLE DB provider "OraOLEDB.Oracle" for linked server ....."這個(gè)坑

?

SELECT?TOP 10 * FROM?OPENROWSET('OraOLEDB.Oracle', 'ESCM_134';'test';'test', 'SELECT * FROM TEST.MY_SET')

Msg 7399, Level 16, State 1, Line 1

The OLE DB provider "OraOLEDB.Oracle" for linked server "(null)" reported an error. Access denied.

Msg 7350, Level 16, State 2, Line 1

Cannot get the column information from OLE DB provider "OraOLEDB.Oracle" for linked server "(null)".

?

?

解決這個(gè)也比較簡(jiǎn)單，使用SSMS連接到數(shù)據(jù)庫后，在“Server Objects”->"Linked Servers"->"OraOLEDB.Oracle"下勾選“Allow inprocess"選項(xiàng)。注意，如果不重啟，無法使之生效，依然會(huì)報(bào)上面錯(cuò)誤。

?

?

3：遇到“OLE DB provider "OraOLEDB.Oracle" for linked server "(null)" returned message "ORA-12154: TNS: 無法解析指定的連接標(biāo)識(shí)符...."這個(gè)坑

SELECT?TOP 10 * FROM?OPENROWSET('OraOLEDB.Oracle', 'ESCM_134';'test';'test', 'SELECT * FROM TEST.MY_SET')

OLE DB provider "OraOLEDB.Oracle" for linked server "(null)" returned message "ORA-12154: TNS: 無法解析指定的連接標(biāo)識(shí)符".

Msg 7303, Level 16, State 1, Line 1

Cannot initialize the data source object of OLE DB provider "OraOLEDB.Oracle" for linked server "(null)".

?

遇到這個(gè)問題有幾種情形：

1： 你沒有在TNS配置文件里面配置相關(guān)ORACLE實(shí)例信息。

2： SQL Server數(shù)據(jù)庫是64位的，你只安裝了32bit數(shù)據(jù)庫，配置了Oracle Client 32bit下的TNS，或者Oracle Client 32/64位都安裝了，但是你只配置了32位下的TNS。其實(shí)只需要配置64下的TNS即可。因?yàn)?4位的SQL Server肯定調(diào)用64位的驅(qū)動(dòng)程序。

?

4：普通賬號(hào)遇到“Ad hoc access to OLE DB provider 'OraOLEDB.Oracle' has been denied. You must access this provider through a linked server."錯(cuò)誤， 具有sysadmin角色的賬號(hào)執(zhí)行下面SQL正常，但是非常普通的賬號(hào)就一直報(bào)下面錯(cuò)誤

?

?

SELECT TOP 10 * FROM OPENROWSET('OraOLEDB.Oracle', 'ESCM_134';'test';'test', 'SELECT * FROM TEST.MY_SET')

Msg 7415, Level 16, State 1, Line 1

Ad hoc access to OLE DB provider 'OraOLEDB.Oracle' has been denied. You must access this provider through a linked server.

?

解決方法，在服務(wù)器打開注冊(cè)表，在HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL12.MSSQLSERVER\Providers\OraOLEDB.Oracle下（當(dāng)然不同版本或命名實(shí)例的數(shù)據(jù)庫，這個(gè)注冊(cè)表路徑有啥不同，根據(jù)實(shí)際情況找到OraOLEDB.Oracle），新建DisallowAdHocAccess選項(xiàng)即可解決問題。

?

?

DisallowAdHocAccess屬性設(shè)置為 1，SQL Server 不允許特別通過 OPENROWSET 和 OPENDATASOURCE 函數(shù)根據(jù)指定的 OLE DB 提供程序訪問。如果您嘗試調(diào)用這些函數(shù)中的特殊查詢，您會(huì)收到類似于以下內(nèi)容的錯(cuò)誤消息

?

• A change of the value of DisallowAdHocAscess from 1 to 0 would not require a restart of the SQL Service, whereas a change from 0 to 1 would have to have a SQL Service restart for the change that was made to become effective.
• With the DisallowAdHocAccess property set to 1, SQL Server does not allow ad hoc access through the OPENROWSET and the OPENDATASOURCE functions against the specified OLE DB provider. If you try to call these functions in ad hoc queries, you receive an error message that resembles the following:

  Server: Msg 7415, Level 16, State 1, Line 1 Ad hoc access to OLE DB provider 'Microsoft.Jet.OLEDB.4.0' has been denied. You must access this provider through a linked server.

  In other words, with the DisallowAdHocAccess property set to 1 for a specific OLE DB provider, you must use a predefined linked server setup for the specific OLE DB provider. You can no longer pass in an ad hoc connection string that references that provider to the OPENROWSET or the OPENDATASOURCE function.

?

參考資料：

https://support.microsoft.com/zh-cn/kb/327489

?

轉(zhuǎn)載于:https://www.cnblogs.com/kerrycode/p/5784805.html

總結(jié)

以上是生活随笔為你收集整理的SQL Server 使用OPENROWSET访问ORACLE遇到的各种坑总结的全部?jī)?nèi)容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網(wǎng)站內(nèi)容還不錯(cuò)，歡迎將生活随笔推薦給好友。