Forum Stats

  • 3,751,294 Users
  • 2,250,339 Discussions
  • 7,867,376 Comments

Discussions

Error using ODBC link to MSSQL, ORA-28545

van Dommelen
van Dommelen Member Posts: 39 Blue Ribbon
edited Jul 22, 2016 9:52AM in Heterogeneous Connectivity

We get this error when trying to connect to the MSSQL 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 MSSQL

Listener configuration

Listener

# listener.ora Network Configuration File: C:\Oracle\product\11.2.0\dbhome_1\network\admin\listener.ora

# Generated by Oracle configuration tools.

LISTENER =

  (DESCRIPTION_LIST =

    (DESCRIPTION =

      (ADDRESS = (PROTOCOL = TCP)(HOST = GL-SRV01)(PORT = 1521))

      (ADDRESS = (PROTOCOL = TCP)(HOST = GL-SRV01)(PORT = 1522))

      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))

    )

  )

SID_LIST_LISTENER =

  (SID_LIST =

    (SID_DESC =

      (SID_NAME = CLRExtProc)

      (ORACLE_HOME = C:\Oracle\product\11.2.0\dbhome_1)

      (PROGRAM = extproc)

      (ENVS = "EXTPROC_DLLS=ONLY:C:\Oracle\product\11.2.0\dbhome_1\bin\oraclr11.dll")

    )

    (SID_DESC=

       (SID_NAME=dg4msql)

       (ORACLE_HOME=C:\Oracle\product\11.2.0\dbhome_1)

       (PROGRAM=dg4msql)

    )

    (SID_DESC=

      (SID_NAME = MSSQL)

      (ORACLE_HOME=C:\Oracle\product\11.2.0\dbhome_1)

      (PROGRAM = hsodbc)

    )

  )

# tnsnames.ora Network Configuration File: C:\Oracle\product\11.2.0\dbhome_1\network\admin\tnsnames.ora

# Generated by Oracle configuration tools.

ORACLR_CONNECTION_DATA =

  (DESCRIPTION =

    (ADDRESS_LIST =

      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))

    )

    (CONNECT_DATA =

      (SID = CLRExtProc)

      (PRESENTATION = RO)

    )

  )

ABS1 =

  (DESCRIPTION =

    (ADDRESS = (PROTOCOL = TCP)(HOST = GL-SRV01)(PORT = 1521))

    (CONNECT_DATA =

      (SERVER = DEDICATED)

      (SERVICE_NAME = ABS1)

    )

  )

dg4msql  =

  (DESCRIPTION=

    (ADDRESS=(PROTOCOL = TCP)(HOST = GL-SRV01)(PORT=1522))

    (CONNECT_DATA= (SID = dg4msql))

    (HS = OK)

  )

MSSQL =

   (DESCRIPTION =  

    (ADDRESS_LIST =    

     (ADDRESS = (PROTOCOL = tcp)(host=GL-SRV01)(port=1522))

    )  

    (CONNECT_DATA = (SID = MSSQL))

   (HS=OK)

  )

When doing tnsping it works

Used TNSNAMES adapter to resolve the alias

Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = tcp)

(host=GL-SRV01)(port=1522))) (CONNECT_DATA = (SID = MSSQL)) (HS=OK))

OK (10 msec)

Best Answer

  • Kgronau-Oracle
    Kgronau-Oracle Posts: 2,550 Employee
    edited Jun 7, 2016 6:24AM Accepted Answer

    The executable of the gateway is called dg4odbc in 11g onwards - hsodbc was the old name for versions <11g.

    So correct in your listener.ora file the program=hsodbc entry to:

          (SID_NAME = MSSQL)

          (ORACLE_HOME=C:\Oracle\product\11.2.0\dbhome_1)

          (PROGRAM = dg4odbc)

    Then stop and start the listener and test again.

    If it still fails with the same error message, please post the output of:

    lsnrctl status

    C:\Oracle\product\11.2.0\dbhome_1\bin\dg4odbc

    Both executed in a command line window.

    - Klaus

    van Dommelen
«1345

Answers

  • Kgronau-Oracle
    Kgronau-Oracle Posts: 2,550 Employee
    edited Jun 7, 2016 6:24AM Accepted Answer

    The executable of the gateway is called dg4odbc in 11g onwards - hsodbc was the old name for versions <11g.

    So correct in your listener.ora file the program=hsodbc entry to:

          (SID_NAME = MSSQL)

          (ORACLE_HOME=C:\Oracle\product\11.2.0\dbhome_1)

          (PROGRAM = dg4odbc)

    Then stop and start the listener and test again.

    If it still fails with the same error message, please post the output of:

    lsnrctl status

    C:\Oracle\product\11.2.0\dbhome_1\bin\dg4odbc

    Both executed in a command line window.

    - Klaus

    van Dommelen
  • van Dommelen
    van Dommelen Member Posts: 39 Blue Ribbon
    edited Jun 7, 2016 6:37AM

    The database link now works.

    However, we do get now another message, when we try and access the data in the MSSQL database.

    Error starting at line : 1 in command -

    select *

    from [email protected]

    Error report -

    SQL Error: ORA-28500: Verbinding van ORACLE met een niet-Oracle systeem heeft het volgende bericht geretourneerd.

    [Microsoft][SQL Server Native Client 11.0]String data, right truncation {01004}[Microsoft][SQL Server Native Client 11.0]String data, right truncation {01004}[Microsoft][SQL Server Native Client 11.0]String data, right truncation {01004}[Microsoft][SQL Server Native Client 11.0]String data, right truncation {01004}[Microsoft][SQL Server Native Client 11.0]String data, right truncation {01004}

    ORA-02063: Voorgaande 2 lines uit MSSQL.

    28500. 00000 -  "connection from ORACLE to a non-Oracle system returned this message:"

    *Cause:    The cause is explained in the forwarded message.

    *Action:   See the non-Oracle system's documentation of the forwarded

               message.

  • Kgronau-Oracle
    Kgronau-Oracle Posts: 2,550 Employee
    edited Jun 7, 2016 6:49AM

    Might be an issue with the ODBC driver. Here we need the table description as it is defined at the MS SQL Server side as well as a gateway trace level 255. As the trace might become very large I would suggest to file a SR.

    - Klaus

  • van Dommelen
    van Dommelen Member Posts: 39 Blue Ribbon
    edited Jun 7, 2016 9:56AM

    An error was encountered performing the requested operation:

    ORA-28545: Diagnose van fout door Net8 bij maken van verbinding met een agent.

    Unable to retrieve text of NETWORK/NCR message 65535

    ORA-02063: Voorgaande 2 lines uit MSSQL.

    28545. 0000 -  "error diagnosed by Net8 when connecting to an agent"

    *Cause:    An attempt to call an external procedure or to issue SQL

               to a non-Oracle system on a Heterogeneous Services database link

               failed at connection initialization.  The error diagnosed

               by Net8 NCR software is reported separately.

    *Action:   Refer to the Net8 NCRO error message.  If this isn't clear,

               check connection administrative setup in tnsnames.ora

               and listener.ora for the service associated with the

               Heterogeneous Services database link being used, or with

               'extproc_connection_data' for an external procedure call.

    Vendor code 28545

    That is the message we now get all the time, after trying to make the link to the MSSQL database....

  • Kgronau-Oracle
    Kgronau-Oracle Posts: 2,550 Employee
    edited Jun 7, 2016 10:16AM

    28545 is a configuration issue. Please post your current config (listener including status output), tnsnames.ora and the create database link statement (hash out the password!).

    - Klaus

  • van Dommelen
    van Dommelen Member Posts: 39 Blue Ribbon
    edited Jun 7, 2016 10:32AM

    # listener.ora Network Configuration File: C:\Oracle\product\11.2.0\dbhome_1\network\admin\listener.ora

    # Generated by Oracle configuration tools.

    LISTENER =

      (DESCRIPTION_LIST =

        (DESCRIPTION =

          (ADDRESS = (PROTOCOL = TCP)(HOST = GL-SRV01)(PORT = 1521))

          (ADDRESS = (PROTOCOL = TCP)(HOST = GL-SRV01)(PORT = 1522))

          (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))

        )

      )

    SID_LIST_LISTENER =

      (SID_LIST =

        (SID_DESC =

          (SID_NAME = CLRExtProc)

          (ORACLE_HOME = C:\Oracle\product\11.2.0\dbhome_1)

          (PROGRAM = extproc)

          (ENVS = "EXTPROC_DLLS=ONLY:C:\Oracle\product\11.2.0\dbhome_1\bin\oraclr11.dll")

        )

        (SID_DESC=

          (SID_NAME=dg4msql)

          (ORACLE_HOME=C:\Oracle\product\11.2.0\dbhome_1)

          (PROGRAM=dg4msql)

          (ENVS=LD_LIBRARY_PATH=C:\Oracle\product\11.2.0\dbhome_1\dg4msql\driver\lib;C:\Oracle\product\11.2.0\dbhome_1)

        )

        (SID_DESC=

          (SID_NAME=MSSQL)

          (ORACLE_HOME=C:\Oracle\product\11.2.0\dbhome_1)

          (PROGRAM=dg4odbc)

        )

      )

    # tnsnames.ora Network Configuration File: C:\Oracle\product\11.2.0\dbhome_1\network\admin\tnsnames.ora

    # Generated by Oracle configuration tools.

    ORACLR_CONNECTION_DATA =

      (DESCRIPTION =

        (ADDRESS_LIST =

          (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))

        )

        (CONNECT_DATA =

          (SID = CLRExtProc)

          (PRESENTATION = RO)

        )

      )

    ABS1 =

      (DESCRIPTION =

        (ADDRESS = (PROTOCOL = TCP)(HOST = GL-SRV01)(PORT = 1521))

        (CONNECT_DATA =

          (SERVER = DEDICATED)

          (SERVICE_NAME = ABS1)

        )

      )

    dg4msql  =

      (DESCRIPTION=

        (ADDRESS=(PROTOCOL = TCP)(HOST = GL-SRV01)(PORT=1522))

        (CONNECT_DATA= (SID = dg4msql))

        (HS = OK)

      )

    MSSQL =

       (DESCRIPTION =    

        (ADDRESS_LIST =      

         (ADDRESS = (PROTOCOL = tcp)(host=GL-SRV01)(port=1522))

        )    

        (CONNECT_DATA = (SID = MSSQL))

       (HS=OK)

      )

    ###database link

    create public database link mssql connect to 'sa' identified by <SQLPASWORD>  using 'MSSQL';

  • van Dommelen
    van Dommelen Member Posts: 39 Blue Ribbon
    edited Jun 7, 2016 10:33AM

    Used TNSNAMES adapter to resolve the alias

    Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = tcp)

    (host=GL-SRV01)(port=1522))) (CONNECT_DATA = (SID = MSSQL)) (HS=OK))

    OK (10 msec)

    tnsping works..

  • Kgronau-Oracle
    Kgronau-Oracle Posts: 2,550 Employee
    edited Jun 7, 2016 10:36AM

    what about the listener status?

    When you type at the command prompt: C:\Oracle\product\11.2.0\dbhome_1\bin\dg4odbc - what's the output?

    And by the way, is your Oracle installation 32 or 64bit?

  • van Dommelen
    van Dommelen Member Posts: 39 Blue Ribbon
    edited Jun 7, 2016 10:38AM

    #listner status

    Copyright (c) 1991, 2013, Oracle.  All rights reserved.

    Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=GL-SRV01)(PORT=1521)))

    STATUS of the LISTENER

    ------------------------

    Alias                     LISTENER

    Version                   TNSLSNR for 64-bit Windows: Version 11.2.0.4.0 - Produ

    ction

    Start Date                07-JUN-2016 16:07:06

    Uptime                    0 days 0 hr. 30 min. 26 sec

    Trace Level               off

    Security                  ON: Local OS Authentication

    SNMP                      OFF

    Listener Parameter File   C:\Oracle\product\11.2.0\dbhome_1\network\admin\listen

    er.ora

    Listener Log File         C:\Oracle\diag\tnslsnr\GL-SRV01\listener\alert\log.xml

    Listening Endpoints Summary...

      (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=GL-SRV01)(PORT=1521)))

      (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=GL-SRV01)(PORT=1522)))

      (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\EXTPROC1521ipc)))

    Services Summary...

    Service "ABS1XDB" has 1 instance(s).

      Instance "abs1", status READY, has 1 handler(s) for this service...

    Service "CLRExtProc" has 1 instance(s).

      Instance "CLRExtProc", status UNKNOWN, has 2 handler(s) for this service...

    Service "MSSQL" has 1 instance(s).

      Instance "MSSQL", status UNKNOWN, has 1 handler(s) for this service...

    Service "abs1" has 1 instance(s).

      Instance "abs1", status READY, has 1 handler(s) for this service...

    Service "dg4msql" has 1 instance(s).

      Instance "dg4msql", status UNKNOWN, has 1 handler(s) for this service...

    The command completed successfully

  • van Dommelen
    van Dommelen Member Posts: 39 Blue Ribbon
    edited Jun 7, 2016 10:39AM

    Oracle is 64 bit and so is the MSSQL.

This discussion has been closed.