64-bit SQL Server2008 link Access Oracle Process Rollup workaround record

64-bit SQL Server2008 link Access Oracle Process Rollup workaround record

After a few days of online search for information, experiments, and finally unicom.

Environment: System: Win, SqlServer2008 R2, Connection oracle10g

On the SqlServer2008 R2 machine, you need to install the Oracle client 32-bit and 64-bit two, and then configure the connection alias. Set up the registry. See below for detailed information

1. Error 1

------------------------------------------------

Create ole_db link error on 64-bit machine

The error content is: ' because the ole_db access ' MSDAORA ' is configured to run in single-threaded apartment mode , the Access interface cannot be used for distributed queries '

After investigation, the following suggestions are sent to the customer:

-------------------------------------------------------------------
cause: Not available in 64-bit SQL engine MSDAORA, You can use oraoledb.oracle to install an Oracle 64-bit client.


Suggested Scenarios:
Scenario one: SQL Server side installs 64-bit Oracle 11g R2 Client


2. Error 2:

Error message:

Msg 7302, Level 16, State 1, procedure nc_procsimoutgeneral, line 18th

Unable to create OLE DB provider for linked server "NC " instance of OraOLEDB.Oracle ".

Workaround:
[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\mssql.1\providers\oraoledb.oracle.1]
"AllowInProcess" =dword:00000001
If there is no oraoledb.oracle below the providers, the new 3. Error 3Error message: "OraOLEDB.Oracle" returned the message "ORA-12154:TNS: Unable to resolve the specified connection identifier the OLE DB provider for the link server" NC "OraOLEDB.Oracle returned the message" Ora-12154:tns: Unable to resolve the specified connection identifier. Msg 7303, Level 16, State 1, line 2nd failed to initialize the data source object of the OLE DB provider "OraOLEDB.Oracle" of the linked server "NC". Workaround: This typically does not identify the 64-bit client's connection alias, so the Oracle client 32-bit client needs to be installed. : You also need to set the registry

Locate the registry key Hkey_local_machinesoftwaremicrosoftmsdtcmtxoci

The OracleOciLib value is changed to Oci.dll,

The OracleSqlLib value is changed to Orasql11.dll,

The OracleXaLib value is changed to Oraclient11.dll.

Scenario Two: Download the latest driver
Download a ace.oledb.12.0 for X64 bit driver and change the connection string msdaora to microsoft.ace.oledb.12.0

Ace. oledb.12.0 for X64-bit driver Microsoft Official:
http://www.microsoft.com/zh-cn/download/details.aspx?id=13255

Note: View the methods of accessing the provider of a remote connection, view-Object Explorer-server object-connect to server-"Access interface"

64-bit SQL Server2008 link Access Oracle Process Rollup workaround record