一:环境
操作系统环境:Windos Server 2008 R2 64位
所需软件版本:SQLSERVER 2008 R2 64位
Oracle 11g client 32位和Oracle 11g 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对它做同样的修改
四、原文
- Download 32 and 64-bit clients fromhttp://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)
- Run 32-Bit install on the SQL Server. Select "Custom"
- For Oracle Base, enter "C:\Oracle"
- For "Software Location", change to C:\Oracle\product\11.2.0\client_32
- Choose the following components:
- SQL*Plus
- Oracle Call Interface (OCI)
- Oracle Net
- Oracle Services for Microsoft Transaction Server
- Oracle Administration Assistant for Windows
- Oracle Provider for OLE DB
- Oracle Data Provider for .NET
- Oracle Providers for ASP.NET
- Repeat above steps with the 64-bit installer. HOWEVER, change the "Software Location" to C:\Oracle\product\11.2.0\client_64
- I got a memory error at this point, but chose to ignore it since I know there is enough
- Ignore the error "OracleMTSRecoveryService already exists"
Now that the install is done, just need to tweak a few things.
- Make registry changes to HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSDTC\MTxOCI
- OracleOciLib should be oci.dll
- OracleSqlLib should be orasql11.dll
- OracleXaLib should be oraclient11.dll
- Makesameregistry changes to HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Microsoft\MSDTC\MTxOCI
- Create or copy a new tnanames.ora in C:\Oracle\product\11.2.0\client_32\network\admin
- Create or copy a new tnanames.ora in C:\Oracle\product\11.2.0\client_64\network\admin
- Reboot!
- You should now see OraOLEDB.Oracle as a provider under Linked Servers\Providers in SSMS
- Right-click this provider, then Properties, then tick the box for "Allow Inprocess"
- 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