Cannot obtain the required interface ("IID_IDBCreateCommand") from OLE DB provider "OraOLED...

今天遇到了一个关于 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:

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  

<font style="font-size: 12pt">SELECT * FROM DEV..SCOTT.EMP</font>

You receive the following error

<font style="font-size: 12pt">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].</font>

If you change the query so that it will not return any rows it runs successfully

<font style="font-size: 12pt">SELECT * FROM DEV..SCOTT.EMP where 1=0</font>

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.

<font style="font-size: 12pt">(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\<strong>sqlservr.exe</strong>)(HOST=GBEDNARS-PC)
(USER=SYSTEM))))</font>

    Out-Of-Process Trace:

<font style="font-size: 12pt">(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\<strong>DllHost.exe</strong>)(HOST=GBEDNARS-PC)(USER=SYSTEM))))</font>

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-Process trace 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

1. Open the registry and check the value of the   AllowInProcess   key being used by the Oracle Provider for OLE DB
   

   <font style="font-size: 12pt">HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\Providers\OraOLEDB.Oracle
   
        AllowInProcess     REG_DWORD     0x00000000 (0)</font>

2. 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.
   

   <font style="font-size: 12pt">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)        
           </font>

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:

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)