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 32 bit client on 64 SQL Server 2008 Expand / Collapse
Author
Message
Posted Wednesday, June 2, 2010 5:05 PM


SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, June 24, 2013 4:31 PM
Points: 41, Visits: 132
Specs:
Windows Server 2003 R2 x64 SP2
SQL Server 2008 10.0.2531 (64 bit)
Visual Studio 2008 v9.0.30729.1 SP
.NET Framework v 3.5 SP1


We have SSIS packages that pull data from Oracle databases (9i, 10g) and insert into our SQL Server. I have installed the Oracle 11g R2 64 bit and 32 bit clients (Administrator) on the SQL Server machine. The packages run just fine in 64 bit mode (through the Job Agent), but when I try to run the packages in 32 bit mode, they fail with the following error:

Attempt to load Oracle client libraries threw BadImageFormatException. This problem will occur when running in 64 bit mode with the 32 bit Oracle client components installed. ---> System.BadImageFormatException: An attempt was made to load a program with an incorrect format. (Exception from HRESULT: 0x8007000B) at System.Data.Common.UnsafeNativeMethods.OCILobCopy2(IntPtr svchp, IntPtr errhp, IntPtr dst_locp, IntPtr src_locp, UInt64 amount, UInt64 dst_offset, UInt64 src_offset) at System.Data.OracleClient.OCI.DetermineClientVersion()

From what I've read online, you should be able to install 32 bit and 64 bit clients side by side and both should work. I've tried installing 32 bit followed by 64 bit, and vice versa. I've rebooted, restarted the SQL Server, restarted IIS, etc. Nothing seems to be working. Has anyone been able to resolve this type of issue?

Also, the clients were installed in the following directories:

Oracle\product\11.2.0\client_32
Oracle\product\11.2.0\client_64


________________________________________
Get some SQLey goodness at Dirt McStain's SQL Blog
Post #931751
Posted Thursday, June 3, 2010 11:17 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Tuesday, January 28, 2014 8:15 AM
Points: 3,065, Visits: 4,639
I'm not an expert but I understand you can run 32 bit client in a 64 bit O/S IF you enable 64 bit mode via Enable32bitAppOnWin64

_____________________________________
Pablo (Paul) Berzukov

Author of Understanding Database Administration available at Amazon and other bookstores.

Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.
Post #932309
Posted Monday, June 7, 2010 1:22 PM


SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, June 24, 2013 4:31 PM
Points: 41, Visits: 132
This is an IIS 6.0 setting, correct? Can you give me a brief how-to in making this change?

Also, I should not that the ADO.NET driver (provided by Microsoft) is the one that displays the above error message. The OLE DB driver (provided by the Oracle Client) acts as if the 32 bit Oracle Client was never installed and shows this error:

The requested OLE DB provider OraOLEDB.Oracle.1 is not registered.


________________________________________
Get some SQLey goodness at Dirt McStain's SQL Blog
Post #933670
Posted Wednesday, June 9, 2010 4:44 PM


SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, June 24, 2013 4:31 PM
Points: 41, Visits: 132
I finally figured it out! The 11g R1 32 bit client succeeded where the R2 client failed. I cleared out the existing installations, installed the 32 bit 11g R1 Oracle Client followed by the 64 bit 11g R2 Oracle Client. I considered doing R1 for both, but R1 requires a separate ODAC download to get the 64 bit OLE DB driver.

One thing to add: the R1 installer lets you rename your HOME, while R2 doesn't. If you don't rename HOME_1 to something else during the R1 installation, R2 uses HOME_1 by default and will overwrite the existing folder groups in your Start menu.


________________________________________
Get some SQLey goodness at Dirt McStain's SQL Blog
Post #935038
Posted Thursday, June 10, 2010 7:41 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Tuesday, January 28, 2014 8:15 AM
Points: 3,065, Visits: 4,639
Fantastic feedback - people will benefit of it for a long time. Great job!

_____________________________________
Pablo (Paul) Berzukov

Author of Understanding Database Administration available at Amazon and other bookstores.

Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.
Post #935802
Posted Sunday, October 17, 2010 4:54 AM


Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, December 3, 2014 1:02 PM
Points: 16, Visits: 71
I am trying to get the 64 bit drivers.. the links given in the oracle site points to the failsafe versions of 64bit drivers.. where to get the 64bit ODAC drivers from? any help would be appreciated..

__________________________________________________________
Yuvaraj
Post #1005851
Posted Wednesday, October 20, 2010 12:29 PM


SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, June 24, 2013 4:31 PM
Points: 41, Visits: 132
Here's the main downloads page:
http://www.oracle.com/technetwork/database/enterprise-edition/downloads/index.html

If you click on see all next to Microsoft Windows (x64), it'll take you to this page:
http://www.oracle.com/technetwork/database/enterprise-edition/downloads/112010-win64soft-094461.html

The second section contains the Client download (win64_11gR2_client.zip).

If you want the ODAC 64 drivers for an old version of the client, then they are found here:
http://www.oracle.com/technetwork/developer-tools/visual-studio/downloads/index.html


________________________________________
Get some SQLey goodness at Dirt McStain's SQL Blog
Post #1007941
Posted Saturday, October 23, 2010 1:21 PM


Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, December 3, 2014 1:02 PM
Points: 16, Visits: 71
Dirt McStain (10/20/2010)
Here's the main downloads page:
http://www.oracle.com/technetwork/database/enterprise-edition/downloads/index.html

If you click on see all next to Microsoft Windows (x64), it'll take you to this page:
http://www.oracle.com/technetwork/database/enterprise-edition/downloads/112010-win64soft-094461.html

The second section contains the Client download (win64_11gR2_client.zip).

If you want the ODAC 64 drivers for an old version of the client, then they are found here:
http://www.oracle.com/technetwork/developer-tools/visual-studio/downloads/index.html


With appreciation to your response.. I feel i need to explain more about my scenario here

Installed:
Windows 2008 R2 OS
SQL Server 2008 R2
Oracle 11g R2 ODAC components (32 bit)
Oracle 11g R2 Client (x64)

My Source and Destination database is Oracle DB. I need to use SSIS for the ETL operation.

I have found 4 types of providers for Oracle connectivity..

1.MS OLE DB provider for Oracle
2.MS provider for Oracle by Attunity
3.Oracle provider for OLE DB
4.Oracle Data Provider for .Net

out of the above 1 & 2 are ruled out.. since (1) is available only for 32 bit runtime environment and (2) is only for SS Enterprise edition customers..


(3) works fine under both the 32 & 64 bit environment.

(4) ODAC works fine under 32 bit runtime environment but not under 64 bit..

I wanted to use ODAC..since it outperforms in the above list.. When I installed my 32 bit drivers, i found
* Oracle Data Provider for .Net
* Oracle Data Access Components
in the list of installed items..

but when i installed 64 bit drivers from oracle. I find only

*Oracle Data Provider for .Net
in the list of installed items..

my package still doesn't run under the 64 bit when i choose ADO source..

Can someone explain what the issue might be here.. I beleive it's cos * Oracle Data Access Components is not mentioned in the installed items during 64-bit client installation..


Yuv



__________________________________________________________
Yuvaraj
Post #1009638
Posted Monday, October 25, 2010 4:23 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, July 15, 2013 8:26 AM
Points: 162, Visits: 69
Dirt McStain (10/20/2010)
Here's the main downloads page:
http://www.oracle.com/technetwork/database/enterprise-edition/downloads/index.html

If you click on see all next to Microsoft Windows (x64), it'll take you to this page:
http://www.oracle.com/technetwork/database/enterprise-edition/downloads/112010-win64soft-094461.html

The second section contains the Client download (win64_11gR2_client.zip).

If you want the ODAC 64 drivers for an old version of the client, then they are found here:
http://www.oracle.com/technetwork/developer-tools/visual-studio/downloads/index.html


The link has ODAC 32 drivers.
Post #1009866
Posted Tuesday, October 26, 2010 10:01 PM


SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, June 24, 2013 4:31 PM
Points: 41, Visits: 132
i don't have it in front of me, but there is a 64-bit ADO.NET driver. I don't remember what it comes up as in the menu, but it's there and it works in 64 bit without any problems for me. When I was testing out different versions of Oracle client, I remember that I ran into some problems. Installing Client 11g R1 32-bit alongside the 11g R2 64-bit driver cleared up all of the issues. The biggest problem is that you need the 32 bit driver while developing in BIDS but the jobs run in 64-bit mode. Installing the R2 32-bit driver caused a lot of problems, so I ended up using R1 instead.

________________________________________
Get some SQLey goodness at Dirt McStain's SQL Blog
Post #1011246
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse