Oracle Database Discussions

Hi There,
I'm trying to establish connectivity between oracle database running 11.1.0.7 64bit on windows 2008 server x64 to sql server 2008 database. Both databases (oracle and SQL) are on the same machine.

I know with the older oracle versions, we used to setup hs connection between the two databases, however, I've been reading note 466225.1 (How To : How to Setup DG4ODBC (Oracle Database Gateway for ODBC) on Windows 32bit) on metalink and it states the following:

>
Please be aware:

- DG4ODBC is first ported to 64bit Windows platforms starting with 11.2. Using a 32bit DG4ODBC on a 64bit Windows operating system is NOT certified.

- HSOLEDB is no longer available for 11g on Windows.
>

So, according to the above, I'm guessing that we won't be able to use dg4odbc to establish the connectivity between the two.. how about the old hsodbc setup under ORACLE_HOME\hs\admin ? would that work? or is there another way that w can do this?

I've been working on this and trying to get it going but not sure if I'm doing something wrong or it just doesn't work here..

On the server I did the following:

1. created the sqlserver DSN and tested the connection and it's all ok.


2. added the following to the tnsnames.ora

SQLCentralODBC  =
  (DESCRIPTION=
    (ADDRESS=(PROTOCOL=tcp)(HOST=188.87.135.90)(PORT=1522))
    (CONNECT_DATA=(SID=SQLCentralODBC))
    (HS=OK)
  ) 

3. added the following to the listener.ora:

LISTENER_ELMSCENTRAL =
 (ADDRESS_LIST=
      (ADDRESS=(PROTOCOL=tcp)(HOST=188.87.135.90)(PORT=1522))
 )

SID_LIST_LISTENER_SQLCENTRAL=
  (SID_LIST=
      (SID_DESC=
         (SID_NAME=SQLCentralODBC)
         (ORACLE_HOME=C:\oracle\product\11.1.0.7)
         (PROGRAM=hsodbc)
      )
  )

4. the listener started ok, and I validated the dsn connection and it's all ok:

C:\Users\oracle>tnsping SQLCentralODBC

TNS Ping Utility for 64-bit Windows: Version 11.1.0.7.0 - Production on 21-MAY-2010 15:47:12

Copyright (c) 1997, 2008, Oracle.  All rights reserved.

Used parameter files:
C:\oracle\product\11.1.0.7\network\admin\sqlnet.ora


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION= (ADDRESS=(PROTOCOL=tcp)(HOST=188.87.135.90)(PORT=1522)) (CONNECT_DATA=(SID=SQLCentralODBC)) (HS
=OK))
OK (10 msec)

C:\Users\oracle>

5. created the db link:

SQL> create database link LM_SQLC connect to sqlc_user identified by xxx using 'SQLCentralODBC';

Database link created.

6. Test the connection:

SQL> select * from event@LM_SQLC;
select * from event@LM_SQLC
                    *
ERROR at line 1:
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 LM_SQLC

SQL>

Any ideas/help will be really appreciated.

Thanks alot.

Edited by: user4415740 on May 21, 2010 12:36 PM

Edited by: user4415740 on May 21, 2010 12:52 PM