Can't connect to Sybase (ASA) via Database Gateway for Sybase
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 3 of 3

Thread: Can't connect to Sybase (ASA) via Database Gateway for Sybase

Hybrid View

  1. #1
    Join Date
    Sep 2009
    Posts
    2

    Can't connect to Sybase (ASA) via Database Gateway for Sybase

    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:
    ...
    ODBC13595http://SQLGetTypeInfo.c314
    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

  2. #2
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    Quote Originally Posted by zfajfr View Post
    Transact-SQL is only supported in Adaptive Server Enterprise (enterprise-class version of Sybase's database)
    Sybase and Microsoft SQL Server Transact-SQL language is the equivalent of Oracle's (PL/SQL + SQL).

    Any SQL command issued against a Sybase database must be Transact-SQL compliant.
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

  3. #3
    Join Date
    Sep 2009
    Posts
    2
    Quote Originally Posted by PAVB View Post
    Sybase and Microsoft SQL Server Transact-SQL language is the equivalent of Oracle's (PL/SQL + SQL).

    Any SQL command issued against a Sybase database must be Transact-SQL compliant.
    OK, so any idea what the Error message (ASA Error -611: Transact-SQL feature not supported) could mean?

    Zdenek

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width