SQL Server 2008 R2 x64连接Oracle解决方案

SQL Server 2008 R2 x64连接Oracle解决方案

 

一：环境

操作系统环境： Windos Server 2008 R2 64bit

       所需 软件版本： SQLSERVER 2008 64 位

                              Oracle client 32 位和 Oracle client 64 位

二：问题

         用 Oracle 的客户端去连接远程 Oracle 数据库可以，但用 SQL SERVER 去连接时，总是报一个 OraOLEDB.Oracle.1 没有提供的 错误。

 

三：解决方法

         0 、先去 Oracle 官网上下载 32 位和 64 位的客户端。地址如下：

         http://www.oracle.com/technetwork/database/enterprise-edition/downloads/index.html     

         1 、安装 32 位的 Oracle 客户端，存放路径 C:/Oracle/product/11.2.0/client_32

    2、 安装 64 位的 Oracle 客户端，存放路径 C:/Oracle/product/11.2.0/client_64

这时会报一个内存不足的警告，忽略掉。

    3 、找到注册表项HKEY_LOCAL_MACHINE/SOFTWARE/Microsoft/MSDTC/MTxOCI

OracleOciLib 值改为oci.dll 、OracleSqlLib 值改为orasql11.dll 、OracleXaLib 值改为oraclient11.dll 。

        4 、HKEY_LOCAL_MACHINE/SOFTWARE/Wow6432Node/Microsoft/MSDTC/MTxOCI 对它做同样的修改

 

第3、4步不处理可能也可以解决该问题。

 

 

四、原文

1.    Download 32 and 64-bit clients from http://www.oracle.com/technetwork/database/enterprise-edition/downloads/index.html (click "see all" to see different versions, otherwise, you'll be downloading the complete Oracle program. Your downloads should be about 600 meg each)
2. Run 32-Bit install on the SQL Server. Select "Custom"
3. For Oracle Base, enter "C:/Oracle"
4. For "Software Location", change to C:/Oracle/product/11.2.0/client_32
5. Choose the following components:
6. SQL*Plus
7. Oracle Call Interface (OCI)
8. Oracle Net
9. Oracle Services for Microsoft Transaction Server
10. Oracle Administration Assistant for Windows
11. Oracle Provider for OLE DB
12. Oracle Data Provider for .NET
13. Oracle Providers for ASP.NET
14. Repeat above steps with the 64-bit installer. HOWEVER, change the "Software Location" to C:/Oracle/product/11.2.0/client_64
15. I got a memory error at this point, but chose to ignore it since I know there is enough
16. Ignore the error "OracleMTSRecoveryService already exists"

Now that the install is done, just need to tweak a few things.

1. Make registry changes to HKEY_LOCAL_MACHINE/SOFTWARE/Microsoft/MSDTC/MTxOCI
2. OracleOciLib should be oci.dll
3. OracleSqlLib should be orasql11.dll
4. OracleXaLib should be oraclient11.dll
5. Make same registry changes to HKEY_LOCAL_MACHINE/SOFTWARE/Wow6432Node/Microsoft/MSDTC/MTxOCI
6. Create or copy a new tnanames.ora in C:/Oracle/product/11.2.0/client_32 /network/admin
7. Create or copy a new tnanames.ora in C:/Oracle/product/11.2.0/client_64 /network/admin
8. Reboot!
9. You should now see OraOLEDB.Oracle as a provider under Linked Servers/Providers in SSMS
10. Right-click this provider, then Properties, then tick the box for "Allow Inprocess"
11. You can now create linked server through the GUI or T-SQL

Good luck!

网址：

http://efreedom.com/Question/2-175257/Install-Oracle-OLE-Drivers-Use-SQL-Server-2008-R2-X64