今天遇到了一个关于linked server查询报错的案例，链接服务器链接oracle数据库，测试没有错误，但是执行脚本的时候，报如下错误：
msg 7399, level 16, state 1, line 1
the ole db provider “oraoledb.oracle” for linked server “xxxx” reported an error. access denied.
msg 7301, level 16, state 2, line 1
cannot obtain the required interface (“iid_idbcreatecommand”) from ole db provider “oraoledb.oracle” for linked server “xxxx”.
其实以前遇到过类似的案例，但是这次案例发生在sql server 2012 (sp4) (kb4018073) – 11.0.7001.0 (x64)下，linked server使用 oracle provider for ole db驱动，跟之前遇到的案例cannot create an instance of ole db provider “oraoledb.oracle” for linked server “xxxxxxx”.有一些区别。解决方案相同，需要在oracle provider for ole db驱动的选项里面勾选“allow inporcess”,或者修改注册表，具体参考下面metalink官方文档。（另外，今天还遇到了很奇葩的事情，设置后，sql 语句在其他数据库执行ok，但是在master库下面就一直报这个错误，但是一段时间后又ok了。十分奇怪，暂时不清楚具体原因）
metalink上using oracle ole db provider and ms sql server to acccess oracle database raise errors 7399 and 7301 (文档 id 396648.1)的详细介绍
symptoms
you are unable to connect to the oracle database when using microsoft sql server’s linked server and the oracle provider for ole db and receive errors messages like
msg 7399, level 16, state 1, line 1
the ole db provider “oraoledb.oracle” for linked server “test” reported an error. the provider reported an unexpected catastrophic failure.
msg 7301, level 16, state 2, line 1
cannot obtain the required interface (“iid_idbcreatecommand”) from ole db provider “oraoledb.oracle” for linked server “test”.
cause
the oracle provider for ole db has been configured to run out-of-process (in a separate process than the sql server process, typically dllhost.exe) but it is mandatory to run the oracle provider for ole db as in-process to function properly with sql server.
solution
please apply solution from
note:333327.1 error “could not execute query against ole db provider ‘oraoledb.oracle'” when querying against an oracle database using microsoft sql server linked server and the oracle provider for ole db
which describes the same problem but with different symptoms.
references
note:333327.1 – error “could not execute query against ole db provider ‘oraoledb.oracle'” when querying against an oracle database using microsoft sql server linked server and the oracle provider for ole db
另外，关于文档333327.1 ——error “could not execute query against ole db provider ‘oraoledb.oracle'” when querying against an oracle database using microsoft sql server linked server and the oracle provider for ole db (文档 id 333327.1)的具体内容如下：
applies to:
oracle provider for ole db – version 10.2.0.1 and later
microsoft windows (32-bit)
***checked for relevance on 10-oct-2016***
symptoms
you are unable to connect to the oracle database when using microsoft sql server’s linked server and the oracle provider for ole db. when issuing the following query from microsoft’s sql query analyzer
select * from dev..scott.emp
you receive the following error
server: msg 7320, level 16, state 2, line 1 could not execute query against ole db provider 'oraoledb.oracle'. ole db error trace [ole/db provider 'oraoledb.oracle' icommandtext::execute returned 0x80040155].
if you change the query so that it will not return any rows it runs successfully
select * from dev..scott.emp where 1=0
cause
the oracle provider for ole db has been configured to run out-of-process (in a separate process than the sql server process, typically dllhost.exe). the oracle provider for ole db must run in-process to function properly with sql server.
by sql*net tracing the failing query you can look at the tns information inside of a sql*net trace you can see the difference between a provider running in and out of process:
in-process trace:
(description=(address_list=(address=(protocol=tcp)(host=gbednars-pc)(port=1521))) (connect_data=(server=dedicated)(service_name=orcl.rmmslang.us.oracle.com) (cid=(program=c:\progra~1\mi6841~1\mssql\binn\sqlservr.exe)(host=gbednars-pc) (user=system))))
out-of-process trace:
(description=(address_list=(address=(protocol=tcp)(host=gbednars-pc)(port=1521))) (connect_data=(server=dedicated)(service_name=orcl.rmmslang.us.oracle.com) (cid=(program=c:\windows\system32\dllhost.exe)(host=gbednars-pc)(user=system))))
in the in-process trace the tns information shows us that the oracle provider for ole db is running under the sqlservr.exe process. in the out-of-processtrace we see that the same provider is running under the dllhost.exe process. dllhost is used as a surrogate process in place of sql server to host out-of-process executions and clearly shows us that the oracle ole db provider has been configured this way.
solution
- open the registry and check the value of the allowinprocess key being used by the oracle provider for ole db
hkey_local_machine\software\microsoft\mssqlserver\providers\oraoledb.oracle allowinprocess reg_dword 0x00000000 (0) - if the allowinprocess key has been set to a value of 0 then it is configured to run out-of-process. change the value from 0 to 1 or if the key does not exist, create it as a dword with a value of 1. the value 1 is also the default setting signifying in-process.
hkey_local_machine\software\microsoft\mssqlserver\providers\oraoledb.oracle allowinprocess reg_dword 0x00000001 (1) or hkey_local_machine\software\microsoft\microsoft sql server\mssql.1\providers\oraoledb.oracle allowinprocess reg_dword 0x00000001 (1)
note: microsoft states that the out-of-process setting allowinprocess=0, is not to be used with any provider other than sqloledb (microsoft’s oledb provider for sql server). see the following link for more information:
microsoft knowledge base article id 833388
you cannot create out-of-process providers with mdac ole db components
additionally, the oracle provider for ole db developer’s guide states that the oracle provider for ole db (oraoledb) is an in-process server.
参考资料
using oracle ole db provider and ms sql server to acccess oracle database raise errors 7399 and 7301 (文档 id 396648.1)
error “could not execute query against ole db provider ‘oraoledb.oracle'” when querying against an oracle database using microsoft sql server linked server and the oracle provider for ole db (文档 id 333327.1)