Solved

Oracle 64bit database link to Ms SQL Server 32bit

Posted on 2011-09-22
7
1,341 Views
Last Modified: 2012-05-12
Hi fellow experts,

- I'm currently in a team working on a project with one of the requirement is to setup a database link from Oracle 10g 64bit (Windows 2008 64bit) to Ms SQL Server 2005 32bit (Windows 2003).
 
- setting up the dblink from Oracle 32bit to Ms SQL Server 32bit works using Oracle Heterogeneous Services ODBC agent, but using the same method on Oracle 64bit to Ms SQL Server 32bit fail with the following error:
ORA-28545: error diagnosed by Net8 when connecting to an agent
Unable to retrieve text of NETWORK/NCR message 65535
ORA-02063: preceding 2 lines from MSSQL

- the team got the Oracle 64-Ms SQL Server 32bit database link works when using Oracle Gateway 11g but we're trying to make it work without using OG if possible.

- we need some ideas, if any of the experts have got a similar setup working, appreciate if you could share the steps/tweak if available.
0
Comment
Question by:OP_Zaharin
  • 4
  • 2
7 Comments
 
LVL 13

Expert Comment

by:George K.
ID: 36586197
Regarding the connection to SQL 32bit you have to use the 32bit driver on the Oracle 64 machine.
0
 
LVL 40

Expert Comment

by:lcohan
ID: 36587539
Unless you upgrade your SQL environment to 64 bits (which is free by the way) and same SQL/OS licenses can be used as far as I'm aware. Other than the pain of the upgrade process itself you should only benefit from running 64 bit SQL Server on 64bit OS.
0
 
LVL 23

Author Comment

by:OP_Zaharin
ID: 36597056
georgekl,
- we have tried both the 32bit and 64bit odbc, both produce the above error mention.

Icohan,
- upgrade the sql server to 64bit is not possible as we have to upgrade our OS too.
0
VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

 
LVL 23

Accepted Solution

by:
OP_Zaharin earned 0 total points
ID: 36901069
hi all,
- we've found a document in Metalink stated that in the Oracle Database 10g dblink using Heterogenous Services is not available on Windows 64bit. It is made available on Oracle Database 11.2.
- the document title is not the same issues as i've raise (mine is 64bit Oracle and Windows to 32bit SQL Server), but in the document it stated as i mention above.
ID 1267101.1: Ora-28545 While Connecting To A 64-Bit SQL*Server Database Using Heterogenous Services

- so i guess it is not possible to use the normal HS on 10g 64bit to connect to a SQL Server.
0
 
LVL 40

Expert Comment

by:lcohan
ID: 36904940
As I mentioned above - and sorry if I wasn't clear enough - you can't use 64 bit to connect to 32 bit and they should be both the same 64 biit.
On Windows 64bit I know you can use 32 bit driver to connect to ORACLE but not viceversa.
0
 
LVL 23

Author Comment

by:OP_Zaharin
ID: 36990111
- Icohan thanks for the reply however as i've posted in  ID: 36901069, Oracle 10g Heterogenous Services itself is not available for 64bit Windows. therefore no dblink can be done except by using Oracle Gateway 11g.
0
 
LVL 23

Author Closing Comment

by:OP_Zaharin
ID: 37035207
as per the oracle doc, Oracle 10g's HS on Windows 64bit is not made available. therefore the workaround is to use Oracle Gateway or Oracle 11g database.
0

Featured Post

NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
ER Diagram 3 21
SSAS Store Forecasting data in the cube 1 20
SQL Recursion 6 22
MS SQL function that converts base-10 to base-36 2 9
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

821 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question