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?