SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Oracle 32 bit client on 64 SQL Server 2008


Oracle 32 bit client on 64 SQL Server 2008

Author
Message
Dirt McStain
Dirt McStain
Old Hand
Old Hand (356 reputation)Old Hand (356 reputation)Old Hand (356 reputation)Old Hand (356 reputation)Old Hand (356 reputation)Old Hand (356 reputation)Old Hand (356 reputation)Old Hand (356 reputation)

Group: General Forum Members
Points: 356 Visits: 138
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
PaulB-TheOneAndOnly
PaulB-TheOneAndOnly
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14625 Visits: 4639
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.
Dirt McStain
Dirt McStain
Old Hand
Old Hand (356 reputation)Old Hand (356 reputation)Old Hand (356 reputation)Old Hand (356 reputation)Old Hand (356 reputation)Old Hand (356 reputation)Old Hand (356 reputation)Old Hand (356 reputation)

Group: General Forum Members
Points: 356 Visits: 138
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
Dirt McStain
Dirt McStain
Old Hand
Old Hand (356 reputation)Old Hand (356 reputation)Old Hand (356 reputation)Old Hand (356 reputation)Old Hand (356 reputation)Old Hand (356 reputation)Old Hand (356 reputation)Old Hand (356 reputation)

Group: General Forum Members
Points: 356 Visits: 138
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
PaulB-TheOneAndOnly
PaulB-TheOneAndOnly
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14625 Visits: 4639
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.
Yuvaraj S-467280
Yuvaraj S-467280
SSC Journeyman
SSC Journeyman (90 reputation)SSC Journeyman (90 reputation)SSC Journeyman (90 reputation)SSC Journeyman (90 reputation)SSC Journeyman (90 reputation)SSC Journeyman (90 reputation)SSC Journeyman (90 reputation)SSC Journeyman (90 reputation)

Group: General Forum Members
Points: 90 Visits: 81
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
Dirt McStain
Dirt McStain
Old Hand
Old Hand (356 reputation)Old Hand (356 reputation)Old Hand (356 reputation)Old Hand (356 reputation)Old Hand (356 reputation)Old Hand (356 reputation)Old Hand (356 reputation)Old Hand (356 reputation)

Group: General Forum Members
Points: 356 Visits: 138
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
Yuvaraj S-467280
Yuvaraj S-467280
SSC Journeyman
SSC Journeyman (90 reputation)SSC Journeyman (90 reputation)SSC Journeyman (90 reputation)SSC Journeyman (90 reputation)SSC Journeyman (90 reputation)SSC Journeyman (90 reputation)SSC Journeyman (90 reputation)SSC Journeyman (90 reputation)

Group: General Forum Members
Points: 90 Visits: 81
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
Yuvaraj Sampath
Yuvaraj Sampath
SSC-Enthusiastic
SSC-Enthusiastic (172 reputation)SSC-Enthusiastic (172 reputation)SSC-Enthusiastic (172 reputation)SSC-Enthusiastic (172 reputation)SSC-Enthusiastic (172 reputation)SSC-Enthusiastic (172 reputation)SSC-Enthusiastic (172 reputation)SSC-Enthusiastic (172 reputation)

Group: General Forum Members
Points: 172 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.
Dirt McStain
Dirt McStain
Old Hand
Old Hand (356 reputation)Old Hand (356 reputation)Old Hand (356 reputation)Old Hand (356 reputation)Old Hand (356 reputation)Old Hand (356 reputation)Old Hand (356 reputation)Old Hand (356 reputation)

Group: General Forum Members
Points: 356 Visits: 138
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search