March 12, 2011 at 4:19 pm
We recently migrated our primary server to Win Server 2008 R2 and SQL Server 2008. It is an OLAP system that downloads updates from two different Oracle systems. The update routines run Access macros to transfer the data.
Wow - what a journey. We never expected that we were entering such poorly charted and implemented waters. To be perfectly honest, we are not Oracle folks, we just want to connect to the servers.
The 32bit vs 64bit driver problem has plagued us since the start. We have operational 32 bit DSNs created using 11g and the Access macros are operational. However, it is our desire to move the data management routines to SSIS and stored procedures using Linked Server connections. For a while we will need all three styles of communication to be operational.
Creating Oracle Linked Servers has been a nightmare. Microsoft documentation (and most discussion threads) point to http://support.microsoft.com/kb/280106 which directs you to create an OLE DB connection using MDAC. In the typical discussion thread the next entry is from an administrator admonishing that MDAC is for client systems and besides, Server 2008 uses WDAC. (Instructions on using WDAC to connect to Oracle DBs are rare)
Other articles/discussions say we should be using MSDAORA but then it seems to get wrapped up in a similar logical loop with admins saying, "Not on Server 2008."
Some threads recommend that after installing the initial Oracle driver set we must then download and install the ODAC. The 11g ODAC set does not want to install and the instructions make a reference to not installing the drivers on a machine with an existing Oracle Home directory. (?@!?!??)
Our most promising instructions were to use the OraOLEDB.Oracle service provider (after remembering to check Allow InProcess) and while everything seemed to go as planned when we test the connection we get a connection error 7302.
Now we have a document in hand that indicates that we should have installed the Oracle drivers before installing SQL Server or any applications. Frankly we are ready to scrape our development server back to a base starting point and start over. All the loading, reloading, and upgrading has seemed to make things quite fragile.
Is there a recommended set of instructions for loading the correct Oracle drivers onto Win Server 2008 r2 so that both 32 bit and 64 bit drivers will function simultaneously?
March 14, 2011 at 3:31 am
I'm also looking for a detailed walkthrough. Myself I usually go through:
1. Install Oracle 64-bit client (11gR2) with Administrator option (not InstantClient, in order to diagnose connectivity), to make sure the latest OPD.NET components are installed, to OracleHome1.
2. Configure OracleHome1 (tnsnames.ora) en test connectivity using sqlplus: sqlplus user/password@mytnsalias
3. Reboot (to make sure the correct registry settings are available in SQLSERVER)
4. Repeat 1-3 with the Oracle 32-bit client to a new OracleHome (OracleHome2). As it installed latest, it should be the default Oracle-Client (ORACLE_HOME variable set)
5. Configure the oracleoledb provider in SQLServer (especially allow in process=true). Too Bad sqlexpress management studio doesn't expose the provider properties. Changed them remote using a full fledged management studio
6. Reboot one more time or restart SQLServer, so the allow in process settings affects new Linked servers
7. Just to be sure, test connectivity via sqlplus (as you have to maintain 2 oraclehomes, 1 for 64-bit, 1 for 32-bit)
8. Setup a linked server using OraOLEDB.Oracle : Provider: Oracle Data Source: mytnsalias
9. Test using OPENQUERY Select Dummy from OPENQUERY (mylinkedserver,'select dummy from dual')
*eliminated ; in step 9
March 16, 2011 at 4:59 am
(I'm also looking for a detailed walkthrough. Myself I usually go through:
1. Install Oracle 64-bit client (11gR2) with Administrator option (not InstantClient, in order to diagnose connectivity), to make sure the latest OPD.NET components are installed, to OracleHome1.
2. Configure OracleHome1 (tnsnames.ora) en test connectivity using sqlplus: sqlplus user/password@mytnsalias
3. Reboot (to make sure the correct registry settings are available in SQLSERVER)
4. Repeat 1-3 with the Oracle 32-bit client to a new OracleHome (OracleHome2). As it installed latest, it should be the default Oracle-Client (ORACLE_HOME variable set)
5. Configure the oracleoledb provider in SQLServer (especially allow in process=true). Too Bad sqlexpress management studio doesn't expose the provider properties. Changed them remote using a full fledged management studio
6. Reboot one more time or restart SQLServer, so the allow in process settings affects new Linked servers
7. Just to be sure, test connectivity via sqlplus (as you have to maintain 2 oraclehomes, 1 for 64-bit, 1 for 32-bit)
8. Setup a linked server using OraOLEDB.Oracle : Provider: Oracle Data Source: mytnsalias
9. Test using OPENQUERY Select Dummy from OPENQUERY (mylinkedserver,'select dummy from dual;')
Nice procedure posted bro!!..
March 3, 2012 at 2:54 pm
Thanks so much for taking the time to write this procedure down. I recently convered to 64 bit and was not able to get a linked server to connect. Following your instructions (verbatim), Everything works great!:-)
May 3, 2012 at 9:46 am
Have you had any experience with OleDB.Oracle? I have installed 11gR2 client and linked server on 2008R2SP1 and queries under 100 rows works fine but anything over gives an error...
I get OLE DB provider "OraOLEDB.Oracle" for linked server "ORA" returned message "ORA-01403: no data found".
Msg 7346, Level 16, State 2, Line 1
May 4, 2012 at 7:26 am
Had no troubles with ora oledb so far. Selecting 10000 rows works fine. Perhaps a mismatch in "case" with the CaSe sensiTive Oracle?
I still need to test against some linked servertroublemakers as "metadata changed" when no precision/scale is specified for an oracle number.
May 4, 2012 at 12:14 pm
The same with rownum < or using top 99 works just fine. So, not sure if this is case issue.
Are you using 2008 R2 SP1 64bit against Oracle 11.1.0.7 64bit as well?
I found a workaround to use the ODBC drivers instead of OLEDB but will have to monitor performance. Thought that OLEDB.Oracle should work fine but nothing yet.
June 14, 2012 at 8:28 am
Maybe it is related to oracle oledb fetchsize (default 100) OraOLEDB-Specific Connection String Attributes for Rowsets
June 14, 2012 at 9:30 am
I wanted to add that there are situations when a machine has an older version of the Oracle client and when you go to install the newer 11g version that no amount of finagling will make that linked server work. This is usually because the registry libraries are still pointed to an old version of oracle client dll files. By doing a little registry surgery to redirect the oracle libraries to the correct updated dll versions installed with the 11g client, the linked server will then start working.
1. Open Regedit32
2. navagate to HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSDTC\MTxOCI:
3. if OracleXaLib is set to xa80.dll then update itto OraClient11.dll
4. if OracleSqlLib is set to SqlLib80.dll then update it to orasql11.dll
5. Navagate to HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Microsoft\MSDTC\MTxOCI
6. if OracleXaLib is set to xa80.dll then update itto OraClient11.dll
7. if OracleSqlLib is set to SqlLib80.dll then update it to orasql11.dll
Just one more thing to check when troubleshooting linked server issues.
June 18, 2012 at 2:52 pm
...
July 9, 2012 at 12:56 pm
muthyala_51, thanks for the extra detail, worked great!
I can add that, in my case:
It looks like my 11gR2 client is version 11.2.0.1.0. The ODAC version that I downloaded was 11.2.0.30 found here:
http://www.oracle.com/technetwork/database/windows/downloads/index-090165.html
For some reason, when I first navigated to the drivers page, I could find the x86 and the x64 client installs, but the ODAC links only pointed to the 32-bit drivers... had to search for the 64 bit flavor.
September 27, 2012 at 2:44 pm
When you make the changes to the registry do I need to restart the server?
Louis
October 6, 2012 at 2:29 pm
Does anyone have a definitive answer for this? This is an absolute nightmare.
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply