oracle dg4odbc set up for sqlserver connection

I've configured Oracle 11g DG4ODBC to talk to Sqlserver 2012 on Window 2008 Oracle server but It came up with error below :

select * from "employee"@tst

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

I've set up the following:

1) Configure ODBC
     System DSN  (DG4ODBC - both 32 and 64 bits) for SQLServer
     It's been tested OK to connect to the SQLserver  from the Oracle server  


2)  Configure Listener

# listener.ora Network Configuration File: D:\app\product\11.2.0\dbhome_1\network\admin\listener.ora
# Generated by Oracle configuration tools.

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
      (ADDRESS = (PROTOCOL = TCP)(HOST = VSYDORA14)(PORT = 1521))
    )
  )

SID_LIST_LISTENER=
  (SID_LIST=
      (SID_DESC=
         (SID_NAME=dg4odbc
         (ORACLE_HOME=D:\app\product\11.2.0\dbhome_1)
         (PROGRAM=dg4odbc)
      )
  )
ADR_BASE_LISTENER = D:\app


3)  Configure TNSNAMES.ORA

 #
#
DG4ODBC =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = tcp)(HOST = vsydora14)(PORT = 1521))
    (CONNECT_DATA =
     (SID = dg4odbc)
    )
    (HS = OK)
  )


4)  Configure DG4ODBC

$ORACLE_HOME/hs/admin/initdg4odbc.ora

# This is a sample agent init file that contains the HS parameters that are
# needed for the Database Gateway for ODBC

#
# HS init parameters
#
HS_FDS_CONNECT_INFO = dg4odbc
HS_FDS_TRACE_LEVEL = OFF
HS_FDS_SUPPORT_STATISTICS = FALSE


5) Restarted Listener
Below is the result of status

c:> Lsnrctl Status

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for 64-bit Windows: Version 11.2.0.1.0 - Produ
ction
Start Date                08-AUG-2014 17:03:33
Uptime                    0 days 0 hr. 5 min. 39 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   D:\app\product\11.2.0\dbhome_1\network\admin\listener.
ora
Listener Log File         d:\app\diag\tnslsnr\VSYDORA14\listener\alert\log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\EXTPROC1521ipc)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=VSYDORA14.osl.local)(PORT=1521)))
Services Summary...
Service "GBLTSTXDB" has 1 instance(s).
  Instance "gbltst", status READY, has 1 handler(s) for this service...
Service "PNGTESTXDB" has 1 instance(s).
  Instance "pngtest", status READY, has 1 handler(s) for this service...
Service "dg4odbc (ORACLE_HOME=D:\app\product\11.2.0\dbhome_1" has 1 instance(s).

  Instance "dg4odbc (ORACLE_HOME=D:\app\product\11.2.0\dbhome_1", status UNKNOWN
, has 1 handler(s) for this service...
Service "gbltst" has 1 instance(s).
  Instance "gbltst", status READY, has 1 handler(s) for this service...
Service "pngtest" has 1 instance(s).
  Instance "pngtest", status READY, has 1 handler(s) for this service...
The command completed successfully

6) TNSPING dg4odbc
It works fine.

Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = tcp)(HOST = vsydora1
4)(PORT = 1521)) (CONNECT_DATA = (SID = dg4odbc)) (HS = OK))
OK (0 msec)


7) Configure Dblink  as below for test

create public database link tst
connect to "user" identified by "pwd" using 'dg4odbc'
/

Both
select user from dual@tst
or
select * from "employee"@tst
generate the error below.

 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

Is there anything missing in the setup above?
Anyone can shed some lights on this?

Many thanks in advance.
OSLEEAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Praveen Kumar ChandrashekatrDatabase Analysist Senior Commented:
Fist check that you are able to communicate to the target Host i.e Oracle DB Host.

from SQL Server machine ping the target Host.
i.e ping <Target_Host>

Also ensure the global_names parameter is set to false.

Is these a 32-bit or 64-bit version, i think you need to provide the library path in listener.ora file

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
      (ADDRESS = (PROTOCOL = TCP)(HOST = VSYDORA14)(PORT = 1521))
    )
  )

SID_LIST_LISTENER=
  (SID_LIST=
      (SID_DESC=
         (SID_NAME=dg4odbc
         (ORACLE_HOME=D:\app\product\11.2.0\dbhome_1)
         (PROGRAM=dg4odbc)
         (ENVS=LD_LIBRARY_PATH=<path>)
      )
  )
ADR_BASE_LISTENER = D:\app
 
i'm also providing you the step by step process for setting up DG
http://www.oratraining.com/blog/2014/01/steps-by-step-connecting-to-microsoft-sql-database-from-oracle-database-using-database-gateway-for-sql-server/
0
Tomas Helgi JohannssonCommented:
Hi!

Oracle is case sensitive so  try changing this

3)  Configure TNSNAMES.ORA

 #
#
DG4ODBC =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = tcp)(HOST = vsydora14)(PORT = 1521))
    (CONNECT_DATA =
     (SID = dg4odbc)
    )
    (HS = OK)
  )

to this

3)  Configure TNSNAMES.ORA

 #
#
dg4odbc =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = tcp)(HOST = vsydora14)(PORT = 1521))
    (CONNECT_DATA =
     (SID = dg4odbc)
    )
    (HS = OK)
  )

Regards,
    Tomas Helgi
0
OSLEEAuthor Commented:
Hi TomasHelgi,
I've tried PING from SQLServer. That was OK.
Also tried was   (ENVS=LD_LIBRARY_PATH=<path>) in Listener.  No Difference. Any more ideas?
0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

OSLEEAuthor Commented:
Hi TomasHelgi,
I've tried the lower case (dg4odbc).  No Difference.  Any more ideas?
0
OSLEEAuthor Commented:
Question?
I thought db4odbc is part of Oracle 11g install and it does not require  a separate install.  
Do I need to download and  install SQLGateway separate from Oracle 11g install?
0
OSLEEAuthor Commented:
Hi praveencpk
 I've tried PING from SQLServer. That was OK.
 Checked that GLOBAL_NAMES = FALSE
 Also tried was   (ENVS=LD_LIBRARY_PATH=<path>) in Listener.  
No Difference.

This is 64 bit version.
Any more ideas?
0
Tomas Helgi JohannssonCommented:
Hi!

Did you download and install the Oracle Gateway  (just to make sure you did ) ?
http://www.oracle.com/technetwork/database/enterprise-edition/downloads/112010-win64soft-094461.html

The reason for this is this
"
If you are using 10.2 HSODBC or TG4MSQL then that is the problem because they were never ported to Windows 64-bit. The only generic connectivity option or gateway available on Windows 64-bit is the 11.2 Database Gateway for ODBC (Dg4ODBC) or Database Gateway for SQL*Server (DG4MSQL)"

see further here https://community.oracle.com/thread/2487313

Also make sure that the database and the gateway are not listening on the same port.

Regards,
      Tomas Helgi
0
Praveen Kumar ChandrashekatrDatabase Analysist Senior Commented:
>>I thought db4odbc is part of Oracle 11g install and it does not require  a separate install.<<
No its not the part of Oracle 11g install.

>>Do I need to download and  install SQLGateway separate from Oracle 11g install?<<
Yes you need to download it has shown in previous post based on the OS and the version.

Here is the installation on configuration doc which will be helpful.
http://docs.oracle.com/cd/E18281_01/doc/gateways.112/e12013/configsql.htm
0
OSLEEAuthor Commented:
I've found out that Oracle 11.2.0 do not come with the latest TG4MSSQL so I got this going via 11.2.0  dblink to Oracle 10G and setting up DG4ODBC from Oracle 10G.  It's working fine now even though it's a work around.

Thanks to all.

I think I close the call as is.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
OSLEEAuthor Commented:
Thanks
0
Steve WalesSenior Database AdministratorCommented:
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.