This discussion is archived
9 Replies Latest reply: Aug 14, 2012 9:53 AM by tx103108 RSS

Error when trying to use ODBC Gateway on Windows7

tx103108 Newbie
Currently Being Moderated
OS: Windows7
DB: Oracle XE 11.2
Gateway: ODBC for Windows 11.2
------------------------------------------------
Appears to be a setup issue on my Windows7 box as I cannot get the ODBC gateway to respond.
Here is the error:

SQL> select * from dual@TXEIS;
select * from dual@TXEIS
*
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 TXEIS

---------------------------------
tnsnames.ora
---------------------------------
TXEIS =
(DESCRIPTION =
(ADDRESS =
(PROTOCOL = TCP)
(HOST = TD-1K5S5R1)
(PORT = 1521)
)
(CONNECT_DATA =
(SID = TXEIS)
)
(HS=OK)
)

--------------------------
listener.ora
--------------------------
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = C:\oraclexe\app\oracle\product\11.2.0\server)
(PROGRAM = extproc)
)
(SID_DESC =
(SID_NAME = CLRExtProc)
(ORACLE_HOME = C:\oraclexe\app\oracle\product\11.2.0\server)
(PROGRAM = extproc)
)
(SID_DESC =
(SID_NAME = TXEIS)
(ORACLE_HOME = C:\oraclexe\app\oracle\product\11.2.0\gtw)
(PROGRAM = db4odbc)
)
)

----------------------
sqlnet.ora
----------------------
# This file is actually generated by netca. But if customers choose to
# install "Software Only", this file wont exist and without the native
# authentication, they will not be able to connect to the database on NT.

SQLNET.AUTHENTICATION_SERVICES = (NTS)
NAMES.DIRECTORY_PATH = (TNSNAMES)

Any advice would be appreciated.
Thanks.
--------------------
initTXEIS.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 = TXEIS
#HS_FDS_TRACE_LEVEL = debug

HS_LANGUAGE=american_america.we8iso8859p1
HS_NLS_NCHAR=UCS2
--------------------------------
tnsping TXEIS works ok.

Any advice would be appreciated.
Thanks.
  • 1. Re: Error when trying to use ODBC Gateway on Windows7
    mkirtley-Oracle Expert
    Currently Being Moderated
    Hi,
    The ORA-28545 error is usually caused by SQL*Net configuration errors.
    In your setup the gateway is installed in C:\oraclexe\app\oracle\product\11.2.0\gtw but you are using a listener started from C:\oraclexe\app\oracle\product\11.2.0\server.
    You should create a listener.ora in the directory -

    C:\oraclexe\app\oracle\product\11.2.0\gtw\network\admin

    with a different port from the listener used by the server and start this listener. Use this new listener for the gateway connection in your tnsnames.ora entry and try again.

    Regards,
    Mike
  • 2. Re: Error when trying to use ODBC Gateway on Windows7
    tx103108 Newbie
    Currently Being Moderated
    Hi Mike,
    I not sure I understand the Oracle Docs. The docs refer to -- as you do -- an Oracle Gateway Listener along with the Oracle Database Listener and that makes sense. But if you look on p.11-4 of the Oracle Database Gateway Installation and Configuration Guide for Windows you'll notice that the documentation mentions that "... if you already have an existing Oracle Net Listener, then add the following syntax to the SID_LIST ..." which suggests to me that the Oracle Gateway Listener is not needed and one could just use the existing Oracle Database Listener (but just add an entry for the gateway). Am I not reading this correctly? I am confused as to whether or not the Gateway Listener is needed considering there already exists an Oracle Database Listener. Please advise and thanks.

    Edited by: user601798 on Aug 13, 2012 8:07 AM
  • 3. Re: Error when trying to use ODBC Gateway on Windows7
    tx103108 Newbie
    Currently Being Moderated
    Just a note -- I've been looking at some other threads and it seems that this issue may also be caused by anti-virus software on Windows systems, causing the connection to fail.

    In my previous architectural approach, I attempted to connect from Linux (OracleXE DB 11.2) to Windows (Sybase Anywhere 10) and could not connect (got a different error -- database not found -- but then again that was from Linux).
    So I switched to Windows (OracleXE DB 11.2) to Windows (Sybase Anywhere 11) and now I get this error (mentioned above).

    I did successfully connect (via the gateway) from Linux (OracleXE DB 11.2) to Linux (Sybase Anywhere 10) where both DBs where on the same Linux sever, using unixODBC and Sybase ODBC drivers.

    Just wanted to give you some further background.
  • 4. Re: Error when trying to use ODBC Gateway on Windows7
    tx103108 Newbie
    Currently Being Moderated
    Still getting the same error after using 2 listeners ( one for gateway, one for the database ):

    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 TXEIS

    ----------------------
    listener.ora for gateway
    ----------------------
    LISTENER =
    (ADDRESS_LIST=
    (ADDRESS=(PROTOCOL=tcp)(HOST=localhost)(PORT=1522))
    )

    SID_LIST_LISTENER=
    (SID_LIST=
    (SID_DESC=
    (SID_NAME=TXEIS)
    (ORACLE_HOME=C:\oraclexe\app\oracle\product\11.2.0\gtw)
    (PROGRAM=dg4odbc)
    )
    )

    #CONNECT_TIMEOUT_LISTENER = 0
    -----------------------
    tnsnames.ora for gateway
    -----------------------
    dg4odbc =
    (DESCRIPTION=
    (ADDRESS=
    (PROTOCOL=tcp)
    (HOST=localhost)(PORT=1521)
    )
    (CONNECT_DATA=
    (SID=dg4odbc))
    (HS=OK))

    TXEIS =
    (DESCRIPTION =
    (ADDRESS =
    (PROTOCOL = TCP)
    (HOST = TD-1K5S5R1)
    (PORT = 1521)
    )
    (CONNECT_DATA =
    (SID = TXEIS))
    (HS=OK))
    ------------------------
    initTXEIS.ora
    ------------------------
    #
    # HS init parameters
    #
    HS_FDS_CONNECT_INFO = TXEIS
    #HS_FDS_TRACE_LEVEL = debug

    HS_LANGUAGE=american_america.we8iso8859p1
    HS_NLS_NCHAR=UCS2
    --------------------
    listener.ora for database
    --------------------
    SID_LIST_LISTENER =
    (SID_LIST =
    (SID_DESC =
    (SID_NAME = PLSExtProc)
    (ORACLE_HOME = C:\oraclexe\app\oracle\product\11.2.0\server)
    (PROGRAM = extproc)
    )
    (SID_DESC =
    (SID_NAME = CLRExtProc)
    (ORACLE_HOME = C:\oraclexe\app\oracle\product\11.2.0\server)
    (PROGRAM = extproc)
    )
    )

    LISTENER =
    (DESCRIPTION_LIST =
    (DESCRIPTION =
    (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
    (ADDRESS = (PROTOCOL = TCP)(HOST = TD-1K5S5R1)(PORT = 1521))
    )
    )

    DEFAULT_SERVICE_LISTENER = (XE)
    ------------------------------
    tnsnames.ora for database
    ------------------------------
    XE =
    (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = TD-1K5S5R1)(PORT = 1521))
    (CONNECT_DATA =
    (SERVER = DEDICATED)
    (SERVICE_NAME = XE)
    )
    )

    TXEIS =
    (DESCRIPTION =
    (ADDRESS =
    (PROTOCOL = TCP)
    (HOST = TD-1K5S5R1)
    (PORT = 1522)
    )
    (CONNECT_DATA =
    (SID = TXEIS))
    (HS=OK))

    EXTPROC_CONNECTION_DATA =
    (DESCRIPTION =
    (ADDRESS_LIST =
    (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
    )
    (CONNECT_DATA =
    (SID = PLSExtProc)
    (PRESENTATION = RO)
    )
    )

    ORACLR_CONNECTION_DATA =
    (DESCRIPTION =
    (ADDRESS_LIST =
    (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
    )
    (CONNECT_DATA =
    (SID = CLRExtProc)
    (PRESENTATION = RO)
    )
    )
    -----------------------
    sqlnet.ora for database
    -----------------------
    SQLNET.AUTHENTICATION_SERVICES = (NTS)
    NAMES.DIRECTORY_PATH = (TNSNAMES)
    ------------------------

    Please advise. Thx.
  • 5. Re: Error when trying to use ODBC Gateway on Windows7
    mkirtley-Oracle Expert
    Currently Being Moderated
    Hi,
    Thanks for setting up a sepaate gateway listener.
    YOu have configured this to use port 1522 but the gateway tnsnames.ora entry you posted shows it is still pointing to port 1521 used by the RDBMS listener-

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

    Change this to -

    dg4odbc =
    (DESCRIPTION=
    (ADDRESS=
    (PROTOCOL=tcp)
    (HOST=localhost)(PORT=1522)
    )
    (CONNECT_DATA=
    (SID=dg4odbc))
    (HS=OK))

    and try a select from a new SQLPLUS session.
    the tnsnames.ora entry for the gateway should be in the RDBMS ORACLE_HOME tnsnames.ora file and not the gateway ORACLE_HOME tnsnames.ora file.

    They are lost in the posting but make sure there is at least one space at he beginning of every line except the first.
    This is also the case for the listener.ora - all lines should have at least one space except -

    LISTENER =
    SID_LIST_LISTENER=

    Regards,
    Mike
  • 6. Re: Error when trying to use ODBC Gateway on Windows7
    tx103108 Newbie
    Currently Being Moderated
    Still no luck. Same error.

    SQL> select * from dual@TXEIS;
    select * from dual@TXEIS
    *
    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 TXEIS

    =============

    Database listener and tnsnames files under C:\oraclexe\app\oracle\product\11.2.0\server\network\ADMIN:
    listener.ora
    SID_LIST_LISTENER =
    (SID_LIST =
    (SID_DESC =
    (SID_NAME = PLSExtProc)
    (ORACLE_HOME = C:\oraclexe\app\oracle\product\11.2.0\server)
    (PROGRAM = extproc)
    )
    (SID_DESC =
    (SID_NAME = CLRExtProc)
    (ORACLE_HOME = C:\oraclexe\app\oracle\product\11.2.0\server)
    (PROGRAM = extproc)
    )
    )

    LISTENER =
    (DESCRIPTION_LIST =
    (DESCRIPTION =
    (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
    (ADDRESS = (PROTOCOL = TCP)(HOST = TD-1K5S5R1)(PORT = 1521))
    )
    )

    DEFAULT_SERVICE_LISTENER = (XE)
    -----------
    tnsnames.ora
    -----------
    XE =
    (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = TD-1K5S5R1)(PORT = 1521))
    (CONNECT_DATA =
    (SERVER = DEDICATED)
    (SERVICE_NAME = XE)
    )
    )

    TXEIS =
    (DESCRIPTION =
    (ADDRESS =
    (PROTOCOL = TCP)
    (HOST = TD-1K5S5R1)
    (PORT = 1522)
    )
    (CONNECT_DATA =
    (SID = TXEIS))
    (HS=OK))

    EXTPROC_CONNECTION_DATA =
    (DESCRIPTION =
    (ADDRESS_LIST =
    (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
    )
    (CONNECT_DATA =
    (SID = PLSExtProc)
    (PRESENTATION = RO)
    )
    )

    ORACLR_CONNECTION_DATA =
    (DESCRIPTION =
    (ADDRESS_LIST =
    (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
    )
    (CONNECT_DATA =
    (SID = CLRExtProc)
    (PRESENTATION = RO)
    )
    )

    Gateway listener and init files under C:\oraclexe\app\oracle\product\11.2.0\gtw\hs\admin (no tnsnames.ora for Gateway):
    ----------
    listerner.ora
    ----------
    LISTENER =
    (ADDRESS_LIST=
    (ADDRESS=(PROTOCOL=tcp)(HOST=localhost)(PORT=1522))
    )

    SID_LIST_LISTENER=
    (SID_LIST=
    (SID_DESC=
    (SID_NAME=TXEIS)
    (ORACLE_HOME=C:\oraclexe\app\oracle\product\11.2.0\gtw)
    (PROGRAM=dg4odbc)
    )
    )

    #CONNECT_TIMEOUT_LISTENER = 0

    ------------------
    initTXEIS.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 = TXEIS
    #HS_FDS_TRACE_LEVEL = debug

    HS_LANGUAGE=american_america.we8iso8859p1
    HS_NLS_NCHAR=UCS2


    QUESTIONS and NOTES:
    (1) There are 2 locations under the Gateway install where there are listener.ora files -- C:\oraclexe\app\oracle\product\11.2.0\gtw\hs\admin and C:\oraclexe\app\oracle\product\11.2.0\gtw\NETWORK\ADMIN. Under which of these 2 directories do I install the listener.ora file for the gateway? The documentation is confusing as it states one directory but then says there are sample files to use (but those are in another directory different from what is stated).

    (2)TXEIS is the 'database' I am trying to connect to. TXEIS represents the ODBC DSN on my Windows system that points to a remote Sybase database running on another Windows system to which I am trying to connect.

    Thanks for all your help... :-)
  • 7. Re: Error when trying to use ODBC Gateway on Windows7
    mkirtley-Oracle Expert
    Currently Being Moderated
    Hi,
    The gateway listener.ora should be in the directory - C:\oraclexe\app\oracle\product\11.2.0\gtw\network\admin.
    Move it there and try again.

    The configuration for the name TXEIS is correct. If this is the ODBC DSN then that is what shouldbe used for the parameter HS_FDS_CONNECT_INFO. However, it must be a System DSN and not a User DSN.

    Regards,
    Mike
  • 8. Re: Error when trying to use ODBC Gateway on Windows7
    tx103108 Newbie
    Currently Being Moderated
    Ok. Getting much warmer!

    Now getting error (going against remote database table sr_accident):

    select * from sr_accident@TXEIS
    *
    ERROR at line 1:
    ORA-01017: invalid username/password; logon denied
    [Sybase][ODBC Driver][SQL Anywhere]Invalid user ID or password {28000,NativeErr
    = -103}
    ORA-02063: preceding 2 lines from TXEIS

    TXEIS is a system DSN using Windows7 64-bit ODBC Admin tool. I can connect via the Windows7 ODBC Admin Tool to the database with this DSN with user "dba" and pw "sql" (no quotes).

    Oracle dblink was created as follows:

    connect sys as sysdba;
    create public database link TXEIS connect to "dba" identified by "sql" using 'TXEIS';

    (please note the quotes in the syntax and verify they are accurate - this SQL is how it is was defined in the Oracle docs).

    Thanks so much -- I feel we are close!
  • 9. Re: Error when trying to use ODBC Gateway on Windows7
    tx103108 Newbie
    Currently Being Moderated
    I resolved this. It would seem that you have to qualify the Sybase tables fully.

    So
    select * from rsccc.sr_accident;

    worked!

    Mike, I want to thank you very much for you help.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points