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

899w71s4pi

　　
一：环境
操作系统环境：Windos Server2008 R2 64bit
       所需软件版本：SQLSERVER2008 64位
                              Oracle client 32 位和Oracleclient 64 位
二：问题
         用Oracle的客户端去连接远程Oracle数据库可以，但用SQLSERVER去连接时，总是报一个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步不处理可能也可以解决该问题。
　　
　　
四、原文

•    Download 32 and 64-bit clients from http://www.oracle.com/technetwork/database/enterprise-edition/downloads/index.html (click "see all" to see differentversions, otherwise, you'll be downloading the complete Oracle program.Your downloads should be about 600 meg each)
  
• Run 32-Bitinstall on the SQL Server. Select "Custom"
  
• For OracleBase, enter "C:\Oracle"
  
• For"Software Location", change to C:\Oracle\product\11.2.0\client_32
  
• Choose thefollowing components:
  
• SQL*Plus
  
• Oracle CallInterface (OCI)
  
• Oracle Net
  
• OracleServices for Microsoft Transaction Server
  
• OracleAdministration Assistant for Windows
  
• OracleProvider for OLE DB
  
• Oracle DataProvider for .NET
  
• OracleProviders for ASP.NET
  
• Repeat abovesteps with the 64-bit installer. HOWEVER, change the "SoftwareLocation" to C:\Oracle\product\11.2.0\client_64
  
• I got amemory error at this point, but chose to ignore it since I know there isenough
  
• Ignore theerror "OracleMTSRecoveryService already exists"
  

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

• Make registrychanges to HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSDTC\MTxOCI
  
• OracleOciLibshould be oci.dll
  
• OracleSqlLibshould be orasql11.dll
  
• OracleXaLibshould be oraclient11.dll
  
• Make sameregistry changes toHKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Microsoft\MSDTC\MTxOCI
  
• Create orcopy a new tnanames.ora in C:\Oracle\product\11.2.0\client_32\network\admin
  
• Create orcopy a new tnanames.ora in C:\Oracle\product\11.2.0\client_64\network\admin
  
• Reboot!
  
• You shouldnow see OraOLEDB.Oracle as a provider under Linked Servers\Providers inSSMS
  
• Right-clickthis provider, then Properties, then tick the box for "AllowInprocess"
  
• You can nowcreate 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