I'm using SQL Server 2005 64 Bit and having a problem
when I try to make a linked server to Oracle 10G 64 Bit (installed on the same
server) from management studio. I'm using "Oracle Provider for OLE DB" to
connect to Oracle 64 Bit database because it is the only provider but from what
I've read in Books Online that the provider doesn't support for Oracle 64 Bit.
When I’m using SSAS 2005 and try to connect to Oracle 64
Bit database, an error message appear when I’m using “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.
Can you please tell me how to fix it ? Thanks in
advance.
Have you installed Oracle client? .NET Oracle provider wouldnt work without it.
Edward Melomed.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
Try contacting customer support and report your problem.
Edward Melomed.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
|||I remember similar problem came up in the past.
Actually it hasnt affected Analysis Server, it only appeared in the BI Dev Studio running on 64bit machine. Try building your project on 32bit machine and deploy to 64bit Analysis Server. See if you get any errors in this case.
Edward Melomed.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
I had the same problem. I worked with the low-level Oracle support folks to no avail. I opened a ticket with Microsoft Dev support and, though they did significantly more investigation they were not able to solve the issue either. At this point, I am in a holding pattern for 64bit until this issue can be resolved. If anyone else has had even partial luck, it would be great to hear what you have tried.
|||
Hi,
We've got the same problem with SQL Server 2005 64-bit SP1. The MS hotfix installation brought us to Build 2153. Oracle 64-bit client software and 10.2.0.2 update has been installed at this server.
It's same parameter you have to set as it is within SQL Server 2000. But now it's within the provider settings.
Start MS SQL Server Management Studio. Go to "Server Objects/Linked Servers/Providers". Choose the properties of OraOLEDB.oracle and check "Allow in process".
My query is running now.
Best Regards,
Uli
|||Dear All,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.
For SSAS 2005 64 bit, I've tried Edward's suggestion that I develop the project in 32 bit server and deploy it to 64 bit server but it doesn't work and the same error still appear because I'm using "Oracle Provider for OLE DB" and SSAS 64 bit doesn't have that provider. I've installed Service Pack 1 and the Hotfix but it doesn't work also. I hope this problem will be solved with Service Pack 2. But is there anyone who knows how to fix this ?
Thanks in advance.|||
Thanks!
I have done that and it works!
|||Thanks
The connection is working for me. But I have one more issue. I cannot view the "nvarchar" column from SQL Server 2005 when I try to see the description of any table from Oracle. I have done the same type of setting with SQL Server 2000 and there are no issues.
Please help.
sqlsql
No comments:
Post a Comment