I'm using SQL Server 2005 64 Bit and having a problem when I try to connect
to Oracle 10G 64 Bit (installed on the same server) from
management studio.

When I try to connect to Oracle 64 Bit database from SSAS 2005, an error
message appear when I use "Microsoft OLE DB Provider for Oracle":

"Test connection failed because of an error in initializing provider. 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.

And also when I'm using "OracleClient Data Provider", an error message
appear:

"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 know how to fix this ? Thanks in advance.

Dear Adrian,

I have found the solution for linked server to connect to Oracle 64 bit
server:

If you want to setup a linked server from SQL 2005 64 bit to Oracle
10g 64 bit, you need to use the "OraOleDb.Oracle" which you can get
from Oracle.

Instructions to Setup the Linked Server Connection
=========================================
- Open SQL Server Management Studio by going to START -- ALL
PROGRAMS -- Microsoft SQL Server 2005

- Provide the Server Name, Authentication, Username and Password to
connect to your
SQL Server at the dialog box. This will authenticate you and
log you into
Management Studio.

- Expand the Server Objects folder by clicking on the + sign

- Now, expand the Linked Servers folder and you will see Providers
folder. Expand
the Providers folder. You will see Oracle Provider for OLEDB.
Right click on it and
go to Properties. Check the check boxes for "Dynamic
Parameters" and "Allow
InProcess". Click OK and the dialogue box will close.

- Next, right click on the Linked Servers folder and choose New
Linked Server

- In the New Linked Server Properties dialogue box, do the
following:
----- Type in a Linked Server Name, for this example, we will call
it 'Tiger'
----- Under Server Type, select Other Data Sources - for the
provider name, select
Oracle Provider for OLEDB
----- In the Data Source field, type in the Oracle Service name
(SQL Net Alias)
----- In the Product Name field, type in "Oracle"
----- Leave Provider String blank.

- Now, click on the "Security option" on the left pane:
----- Select the radio button "Be made using this security context"
at the bottom
of the box.
----- Type in the User ID (your Oracle login), ie. Scott in the
"Remote login"
field and your Password, ie. Tiger in the "With Password"
field.

- Go to the "Server Options" tab in the Linked Server Properties:
----- While leaving the default selections selected, also click on
"Collation
Compatible". Select "true" from the drop down button.
----- Click OK. This will create your Linked Server named Tiger.

Regards,
Albert

adrian menuliskan:

Does Anyone have the link to the oraoledb file?

Albert wrote:

Hi Adrian,

we had similar issues with SQL Server 2005 running on X64.

I got around these by:

1) Installing a 32bit Oracle 10G Client for SQL Server 2005 32bit components.
2) Patching both the 32bit and 64bit Oracle 10G clients:

32bit
Installation of Base Client (10.2.0.1)
Installation of Patch Set 10.2.0.2 (4547817)
Installation of 10.2.0.2 Patch Set 5 (5383042)

64Bit
Installation of 64bit Oracle Components
Installation of Base Client (10.2.0.1)
Installation of Patch Set 10.2.0.2 (4547817)
Installation of 10.2.0.2 Patch Set 6 (5438688) 14

Hope this helps.

Regards
Mick


"adrian" wrote: