Home » RDBMS Server » Networking and Gateways » Can't connect to Sybase (ASA) via Database Gateway for Sybase (Oracle 11g (11.2.0.1.0), Sybase Adaptive Server Anywhere (9.0.2.3527))
Can't connect to Sybase (ASA) via Database Gateway for Sybase [message #421224] Sun, 06 September 2009 08:05 Go to next message
zfajfr
Messages: 2
Registered: September 2009
Junior Member
Hi,
I'm trying to connect from Oracle 11g (11.2.0.1.0) to Sybase Adaptive Server Anywhere (9.0.2.3527). My Oracle environment is running on Linux Centos 5.3, the sybase database runs on a Windows Server.

All my attempts have failed so far - both through Oracle Database Gateway for Sybase and via Oracle Database Gateway for ODBC in combination with freeTDS. Using either way I'm ending up with apparently the same error:

ASA Error -611: Transact-SQL feature not supported

When I use db link created via dg4sybs (Oracle Database Gateway for Sybase) I get this:
SQL> SELECT * FROM aaa@hvx;
SELECT * FROM aaa@hvx
*
ERROR at line 1:
ORA-28500: connection from ORACLE to a non-Oracle system returned this message:
OracleODBC Sybase Wire Protocol driverSybase ASEASA Error -611:
Transact-SQL feature not supported {HY000,NativeErr = 176}
ORA-02063: preceding 2 lines from HVX

My agent init file {$ORACLE_HOME/dg4sybs/admin/initdg4sybs.ora} contains:
# This is a customized agent init file that contains the HS parameters
# that are needed for the Database Gateway for Sybase

#
# HS init parameters
#
#havex_dms is the network alias defined in /etc/hosts pointing to 10.219.11.249
#havex_dms is, at the same time, also a Sybase database name
#using just IP as the server name resulted in "database not found" type of errors
HS_FDS_CONNECT_INFO=havex_dms:2638/havex_dms
#HS_FDS_CONNECT_INFO=10.219.11.249:2638/havex_dms
#HS_FDS_CONNECT_INFO=Havex
HS_FDS_TRACE_LEVEL=DEBUG
HS_FDS_RECOVERY_ACCOUNT=RECOVER
HS_FDS_RECOVERY_PWD=RECOVER

When I use db link created via dg4odbc (Oracle Database Gateway for ODBC) and freeTDS driver I get this:

SQL> SELECT * FROM aaa@hvx1;
SELECT * FROM aaa@hvx1
*
ERROR at line 1:
ORA-28511: lost RPC connection to heterogeneous remote agent using
SID=ORA-28511: lost RPC connection to heterogeneous remote agent using
SID=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=localhost.localdomain)(PORT=1521))
(CONNECT_DATA=(SID=dg4odbc)))
ORA-02063: preceding line from HVX1
Process ID: 12664
Session ID: 49 Serial number: 21020

In the ODBC trace file I can see the same error:
...
ODBC 13595 SQLGetTypeInfo.c 314
Exit:SQL_ERROR
DIAG 42000 FreeTDSSQL ServerASA Error -611: Transact-SQL feature not supported
...

My unixODBC & freeTDS configuration should be OK - I can sucessfully connect via isql:

root@localhost ~# isql -v havex intranet 1234
---------------------------------------
Connected!

sql-statement
help tablename
quit


---------------------------------------
SQL> select @@version;
---------------------------------------------------------------------------------------------------------------------------------
@@version

---------------------------------------------------------------------------------------------------------------------------------
9.0.2.3527

---------------------------------------------------------------------------------------------------------------------------------
SQLRowCount returns 1
1 rows fetched
SQL> select * from aaa;
+-----------+------------+
a b

+-----------+------------+
test 1
pokus 2

+-----------+------------+
SQLRowCount returns 2
2 rows fetched
SQL>


My Oracle Net configuration:

{$ORACLE_HOME/network/admin/listener.ora}
# listener.ora Network Configuration File: /oracle/db_11.2/network/admin/listener.ora
# Generated by Oracle configuration tools.

LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = localhost.localdomain)(PORT = 1521))
)
)

SID_LIST_LISTENER=
(SID_LIST=
(SID_DESC=
(SID_NAME=dg4sybs)
(ORACLE_HOME=/oracle/db_11.2)
(PROGRAM=dg4sybs)
)
(SID_DESC=
(SID_NAME=dg4odbc)
(ORACLE_HOME=/oracle/db_11.2)
(PROGRAM=dg4odbc)
(ENVS=LD_LIBRARY_PATH=/usr/lib:/oracle/db_11.2/lib)
)
)

ADR_BASE_LISTENER = /oracle

{$ORACLE_HOME/network/admin/tnsnames.ora}
# tnsnames.ora Network Configuration File: /oracle/db_11.2/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.

VISION1 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = localhost.localdomain)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = vision1.havex.cz)
)
)

HAVEX_DMS =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = localhost.localdomain)(PORT = 1521))
(CONNECT_DATA =
(SID = dg4sybs)
)
(HS=OK)
)

dg4odbc =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = localhost.localdomain)(PORT = 1521))
(CONNECT_DATA =
(SID = dg4odbc)
)
(HS=OK)
)

It seems both drivers use Transact-SQL instructions which are then denied by ASA. I'm not familiar with Sybase products at all, but as far as what my googling revealed the Transact-SQL is only supported in Adaptive Server Enterprise (enterprise-class version of Sybase's database). I couldn't figure out if there's a way how to disable Transact-SQL in the driver configuration. Has anybody faced (solved) this problem? Is there a proved way how to connect from Oracle (10g or 11g) to Sybase Adaptive Server Anywhere?

Thanks a lot in advance.

Zdenek
Re: Can't connect to Sybase (ASA) via Database Gateway for Sybase [message #421239 is a reply to message #421224] Sun, 06 September 2009 13:10 Go to previous messageGo to next message
babuknb
Messages: 1736
Registered: December 2005
Location: NJ
Senior Member

http://babudba.blogspot.com/search/label/Heterogeneous%20database

This link betweek oracle & Ms-Access but configuration same as sybase also.

Check & let us know
Re: Can't connect to Sybase (ASA) via Database Gateway for Sybase [message #494000 is a reply to message #421224] Thu, 10 February 2011 15:25 Go to previous messageGo to next message
geturchandu
Messages: 8
Registered: July 2006
Junior Member
hi zdenek,

were u able to find a solution for this issue. we are facing exactly the same issue with connection from oracle to sybase.

insert into ttable(select * from STAN_STG where rownum<100000)
this statement works fine and inserts all the rows, but when i try to get 1000000 records it errs out as shown below.

ERROR at line 1:
ORA-28511: lost RPC connection to heterogeneous remote agent using
SID=ORA-28511: lost RPC connection to heterogeneous remote agent using
SID=(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=localhost)(PORT=1551))(CONNECT_DAT
A=(SID=dg4sybs)))
ORA-02063: preceding line from TDB
ORA-06512: at line 9


can u tell me if you found any way to solve the issue.

thanks,
c
Re: Can't connect to Sybase (ASA) via Database Gateway for Sybase [message #494451 is a reply to message #494000] Mon, 14 February 2011 13:36 Go to previous message
zfajfr
Messages: 2
Registered: September 2009
Junior Member
Hi,
my solution was simple - I moved to a MS Windows XP box where the connection from Oracle (both 10gXE and 11g) to Sybase ASA works like a charm. I'm using ODBC driver Adaptive Server Anywhere 9.0 in combination with Oracle Database Gateway for ODBC.

Zdenek
Previous Topic: Connecting to database out side the network
Next Topic: Connecting 32 bit client on 64 bit server
Goto Forum:
  


Current Time: Fri Mar 29 10:59:07 CDT 2024