Operating system environment: Windos Server 2008 R2 64bit
Required software version: SQLSERVER 2008 64 Bits
Oracle client 32-bit and Oracle client 64 Bits
II: the problem
With the Oracle client to connect to remote Oracle database can be, but when used to connect to SQL SERVER, always report did not provide a OraOLEDB.Oracle.1 error.
0, go to the official download Oracle 32-bit and 64-bit client. The following address:
1, install the 32 bit Oracle client and store the path C: \ Oracle \ product \ 11.2.0 \ client_ 32
2, install 64-bit Oracle client and store the path C: \ Oracle \ product \ 11.2.0 \ client_ 64
Will be reported when a low memory warning, ignored.
3, find the registry key HKEY_LOCAL_MACHINE \ SOFTWARE \ Microsoft \ MSDTC \ MTxOCI
OracleOciLib value to oci.dll, OracleSqlLib value to orasql11.dll, OracleXaLib value to oraclient11.dll.
4, HKEY_LOCAL_MACHINE \ SOFTWARE \ Wow6432Node \ Microsoft \ MSDTC \ MTxOCI modify it to do the same
Step 3 and 4 can not handle possible to solve the problem.
Fourth, the original
- 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)
- 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
- Make same registry 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
- 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