Problem creating Oracle Data Sources in SSAS 2008 R2

Question

Problem creating Oracle Data Sources in SSAS 2008 R2

Michael.Gagne

SSC-Addicted

Points: 442
More actions
August 19, 2011 at 2:22 pm

#246556

Hi there,
I'm working on a Windows 2008 server (64 bit), SQL 2008 R2 (64 bit) and I've installed Oracle 11g (64 bit).
My data source is an Oracle 10g database.
I have the data source properly defined in my tnsnames.ora file and I'm able to connect using SQLPlus and I'm also able to query data via a remote server link in SQL 2008 R2.
I'm trying to create a data source in BIDS for a new Analysis project. When I try to create a new data source using OracleClient Data Provider, I get the following error:
"Test connection failed because of an error in initializing provider. 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."
Anyone encounter this problem before and solve it?
Thank you,
Mike

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

PaulB-TheOneAndOnly SSC Guru Points: 66772 More actions · Answer 1

Michael.Gagne (8/19/2011)
Hi there,
I'm working on a Windows 2008 server (64 bit), SQL 2008 R2 (64 bit) and I've installed Oracle 11g (64 bit).
My data source is an Oracle 10g database.
I have the data source properly defined in my tnsnames.ora file and I'm able to connect using SQLPlus and I'm also able to query data via a remote server link in SQL 2008 R2.
I'm trying to create a data source in BIDS for a new Analysis project. When I try to create a new data source using OracleClient Data Provider, I get the following error:
"Test connection failed because of an error in initializing provider. 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."
Anyone encounter this problem before and solve it?

This is not an Oracle but a O/S side issue.

Let me quote Somakorimi post find on http://social.msdn.microsoft.com/Forums/en-US/netfx64bit/thread/b8cb5f3d-8e61-4e63-b325-d07b261f77ac/

"I got the root cause of the problem i posted above (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)

This will happen when IIS is running in 32bit mode in Win2k3 64bit OS system, so change IIS to 64bit mode it will stare working.

1.Type the following command to enable the 64-bit mode:

cscript %SYSTEMDRIVE%\inetpub\adminscripts\adsutil.vbs SET W3SVC/AppPools/Enable32bitAppOnWin64 0

2.Type the following command to install the version of ASP.NET 1.1 and to install the script maps at the IIS root and under:

%SYSTEMROOT%\Microsoft.NET\Framework\v2.0.50727\aspnet_regiis.exe -i "

_____________________________________
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.

manishkumar30 Valued Member Points: 53 More actions · Answer 2

Hi dear,

I have the same configuration. I am using SQL Server 2008 and Oracle 11g in my same system.

my requirement is through SQL Server 2008 Link Server, I want to connect Oracle table and generate the report from SQL Server 2008.

Please help me in this.You can reply me on coderider007@gmail.com

many thanks in advance.

PaulB-TheOneAndOnly SSC Guru Points: 66772 More actions · Answer 3

manishkumar30 (4/14/2012)
I have the same configuration. I am using SQL Server 2008 and Oracle 11g in my same system.
my requirement is through SQL Server 2008 Link Server, I want to connect Oracle table and generate the report from SQL Server 2008.
Please help me in this.

1- Create the link

2- Run the query

Please note that you do not connect to a table but to an Oracle instance where the account you use to connect has to have at least SELECT privileges on the target table.

_____________________________________
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.