MS SQL Data Warehousing - Problems loading data from Oracle

Author

Problems loading data from Oracle

Rafa®

2006-03-15, 7:29 am

I'm having a bad time with Oracle and Analysis Services 2005. We are
migrating a Cube from AS2000 wich uses Oracle as Data Source.

The installation is clean. The machine haves only SQL Server 2005 installed,
with instances for Database Engine and Analysis Services, and client tools
(to develop Integration Services Packages). The client tools from Oracle
9.2.0.1 is installed correctly, I can tnsping my servers and connect to them
from iSQL, also from Visual Studio Server Explorer, I can connect to the
server and explore data correctly.

The problem comes when I try to process the Cube I've migrated from AS2000.
Changing the data source to use OracleClient .Net Provider, display the
following error:

"Errors in the high-level relational engine. The following exception
occurred while the managed IDbConnection interface was being used:
System.Data.OracleClient requires Oracle client software version 8.1.7 or
greater.."

Trying to change the data source, to use OLE DB Provider for Oracle,
displays the following:

"OLE DB error: OLE DB or ODBC error: Oracle client and networking components
were not found. These components are supplied by Oracle Corporation and are
part of the Oracle Version 7.3.3 or later client software installation.

Provider is unable to function until these components are installed.."

I must note that it only occurs with AS trying to process something. I
tested create a new AS database from the scratch... Setting the datasource
and clicking in the Test Connection button, emits me an OK dialog. But, when
I try to process the cube, I get this error depending on the Provider used.

Also, I tried to copy data from Oracle to a local SQL Database, via
Integration Services package, and it reads and copies the data correctly!

Please guys, is there any configuration I must set? Why this error occurs
only in Analysis Services, and not in any other programs that uses the same
providers?

Thanks,
Rafa

Rafa®

2006-03-15, 9:23 am

Guys, I found myself the problem...

I installed the Analysis Service using Network Service Built-in account.
Changing to Local System Built-in Account made it start to work properly.
Probably because the Network Service being so restritive account, doesn't
have access to Oracle components.

Thanks,
Rafa

"Rafa®" wrote:

> I'm having a bad time with Oracle and Analysis Services 2005. We are
> migrating a Cube from AS2000 wich uses Oracle as Data Source.
>
> The installation is clean. The machine haves only SQL Server 2005 installed,
> with instances for Database Engine and Analysis Services, and client tools
> (to develop Integration Services Packages). The client tools from Oracle
> 9.2.0.1 is installed correctly, I can tnsping my servers and connect to them
> from iSQL, also from Visual Studio Server Explorer, I can connect to the
> server and explore data correctly.
>
> The problem comes when I try to process the Cube I've migrated from AS2000.
> Changing the data source to use OracleClient .Net Provider, display the
> following error:
>
> "Errors in the high-level relational engine. The following exception
> occurred while the managed IDbConnection interface was being used:
> System.Data.OracleClient requires Oracle client software version 8.1.7 or
> greater.."
>
> Trying to change the data source, to use OLE DB Provider for Oracle,
> displays the following:
>
> "OLE DB error: OLE DB or ODBC error: Oracle client and networking components
> were not found. These components are supplied by Oracle Corporation and are
> part of the Oracle Version 7.3.3 or later client software installation.
>
> Provider is unable to function until these components are installed.."
>
> I must note that it only occurs with AS trying to process something. I
> tested create a new AS database from the scratch... Setting the datasource
> and clicking in the Test Connection button, emits me an OK dialog. But, when
> I try to process the cube, I get this error depending on the Provider used.
>
> Also, I tried to copy data from Oracle to a local SQL Database, via
> Integration Services package, and it reads and copies the data correctly!
>
> Please guys, is there any configuration I must set? Why this error occurs
> only in Analysis Services, and not in any other programs that uses the same
> providers?
>
> Thanks,
> Rafa

Sponsored Links