Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Oracle Driver Mania Expand / Collapse
Author
Message
Posted Saturday, March 12, 2011 4:19 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, September 16, 2013 9:14 AM
Points: 7, Visits: 48
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?
Post #1077381
Posted Monday, March 14, 2011 3:31 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 8:04 PM
Points: 1,394, Visits: 6,593
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
Post #1077599
Posted Wednesday, March 16, 2011 4:59 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, March 25, 2011 4:05 AM
Points: 20, Visits: 12
(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!!..


Tech Blog
Post #1078852
Posted Saturday, March 3, 2012 2:54 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, September 23, 2014 7:09 AM
Points: 12, Visits: 64
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!
Post #1261194
Posted Thursday, May 3, 2012 9:46 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, May 4, 2012 12:12 PM
Points: 2, Visits: 4
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
Post #1294739
Posted Friday, May 4, 2012 7:26 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 8:04 PM
Points: 1,394, Visits: 6,593
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.
Post #1295225
Posted Friday, May 4, 2012 12:14 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, May 4, 2012 12:12 PM
Points: 2, Visits: 4
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.
Post #1295419
Posted Thursday, June 14, 2012 8:28 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 8:04 PM
Points: 1,394, Visits: 6,593
Maybe it is related to oracle oledb fetchsize (default 100) OraOLEDB-Specific Connection String Attributes for Rowsets
Post #1315933
Posted Thursday, June 14, 2012 9:30 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, September 24, 2014 3:28 PM
Points: 227, Visits: 123
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.



Post #1316000
Posted Monday, June 18, 2012 2:52 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Today @ 1:43 PM
Points: 401, Visits: 2,003
Here are the full steps- how it worked for me.

--------------------------------------------------------
. Download the oracle client "win64_11gR2_client.zip" and windows driver for Oracle "ODAC112021Xcopy_x64.zip" ( both are 64 bit)
. install the win64_11gR2_client.zip with "Administrator mode".
1.Unzip the win64_11gR2_client.zip file into an appropriate location.
2.Run the setup.exe to begin the installation process.
3.Select the "Administrator" installation option. Click Next.
4.Set the Oracle Base to: C:\oracle\app
5.Set the Software Location to: C:\oracle\app\product\11.2.0\client_1
6.Click Next.
7.Wait for the installation to finish.
8.Click Close when the installation has finished.

.To install Oracle Data Access Components (ODAC) InstallationODAC112021Xcopy_x64.zip)
1.Unzip the ODAC112021Xcopy_x64.zip file into an appropriate location.
2.Open a Command Prompt as an "Administrator".
3.Change directory to the location of the ODAC112021Xcopy_x64 contents.

•Example: cd C:\temp\ODAC112021Xcopy_x64\
4.Run the following command in the Administrative Command Prompt:

•install.bat all C:\oracle\app\product\11.2.0\client_1 odac
5.Wait for the command to finish processing.
6.Close the command prompt.
7.A reboot of Windows would be recommended.

. Save the TNSNAMES.ora and SQLNET.ora files in the oracle client location.
example : C:\oracle\app\product\11.2.0\client_1
. Also save the same files at location- C:\oracle\app\product\11.2.0\client_1\Network\Admin
. Add the below three paths to system environment variables:
“ORACLE_HOME=C:\oracle\app\product\11.2.0\client_1" and
add two paths to system path: “C:\oracle\app\product\11.2.0\client_1" and “C:\oracle\app\product\11.2.0\client_1\bin”.
. Now do the "TNSPING dbname" from cmd :\
. do the UDL test to confirm the connection.
. connect to SSMS- Linked server- Providers- "OraOLEDB.Oracle" - properties- enables these below 5 options:
- Dynamic Parameters.
- Nested Queries.
- Allow in Process.
- Non Transacted updates.
- supports 'Like' Operator.
. Restart the MSDTC and sql server services.
. Create the Linked server in SSMS.

---------------------

Note:No Registry values have to be updated or neither the Oracle client 32bit has to be installed.
Post #1317601
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse