1 2 Previous Next 24 Replies Latest reply: Nov 30, 2010 12:11 AM by Kgronau-Oracle RSS

    ORA-28545: error diagnosed by Net8 when connecting to an agent error URGENT

    556874
      Hi,

      I've to setup and use SQL Server 2K db from Oracle10g 10.1.0 db. Basically, i've to
      periodically PULL data from oracle to sql server 2k

      Have done the necessary setup as specified in Oracle docs.

      1. System DSN: MYSQLSERVERDSN

      2. ini file: initMYSQLSERVERDSN.ora
      contents of ini file
      HS_FDS_CONNECT_INFO = MYSQLSERVERDSN
      HS_FDS_TRACE_LEVEL = OFF

      3. tnsnames entry:

      MYSQLSERVERDSN =
      (DESCRIPTION =
      (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = WORKSTATION21)(PORT = 1522))
      )
      (CONNECT_DATA =
      (SID = MYSQLSERVERDSN)
      )
      (HS = OK)
      )


      4. listener.ora entry

      LISTENERMYSQLSERVERDSN =
      (ADDRESS_LIST=
      (ADDRESS=(PROTOCOL=tcp)(HOST=WORKSTATION21)(PORT=1522))
      (ADDRESS=(PROTOCOL=ipc)(KEY=PNPKEY)))

      SID_LIST_LISTENERMYSQLSERVERDSN=
      (SID_LIST=
      (SID_DESC=
      (SID_NAME=MYSQLSERVERDSN)
      (ORACLE_HOME = d:\oracle\product\10.1.0\db_1)
      (PROGRAM=hsodbc)
      )
      )


      Did i miss something here?

      I started the listener by

      c:\> lsnrctl start LISTENERMYSQLSERVERDSN. it started well and
      I tried tnsping, that is also working. Then,

      Did i miss something here? Coz when trying to connect to the tables in
      SQLSERVER2K, get following error:



      Connected to:
      Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - Production
      With the Partitioning, OLAP and Data Mining options


      SQL>create database link mysqlserverdsn connect to xx identified by xxxx using 'MYSQLSERVERDSN';

      Database link created.

      SQL> desc emp@mysqlserverdsn;
      ERROR:
      ORA-28545: error diagnosed by Net8 when connecting to an agent
      NCRO: Failed to make RSLV connection
      ORA-02063: preceding 2 lines from MYSQLSERVERDSN


      I'm not able to figure out where the error lies.

      Any inputs on this?? please help me, I am in urgent.
        • 1. Re: ORA-28545: error diagnosed by Net8 when connecting to an agent error URGENT
          529827
          Hi I think you have to change your tns using .world after the tns name connection.

          regards,
          Joao Lanaro
          • 2. Re: ORA-28545: error diagnosed by Net8 when connecting to an agent error URGENT
            513417
            Can you please elaborate on this, do you mean to say to change the tnsnames.ora file to append .world to the service name just to add domain to it?
            • 3. Re: ORA-28545: error diagnosed by Net8 when connecting to an agent error URGENT
              user600155
              Check the tnsnames.ora in the host you have to write the IP of oracle server.


              MYSQLSERVERDSN =
              (DESCRIPTION =
              (ADDRESS_LIST =
              (ADDRESS = (PROTOCOL = TCP)(HOST = IP ORACLE SERVER )(PORT = 1522))
              )
              (CONNECT_DATA =
              (SID = MYSQLSERVERDSN)
              )
              (HS = OK)
              )
              • 4. Re: ORA-28545: error diagnosed by Net8 when connecting to an agent error URGENT
                Kgronau-Oracle
                28545 error is a configuration issue.

                - please have a look at the tnsnames.ora file and make sure each line except of the tns alias starts at least with 1 space.
                According to the posting all further lines start at position 1... but it might be only a
                copy/paste issue.

                - drop the database link and recreate it. SQL Server usernames and passwords are case sensitiver and thus need to be surrounded by double quotes like:
                create database link mysqlserverdsn connect to "sa" identified by "xxxx" using 'MYSQLSERVERDSN';

                - please provide:
                lsnrctl status LISTENERMYSQLSERVERDSN
                - please provide tnsping MYSQLSERVERDSN
                - please provide: d:\oracle\product\10.1.0\db_1\bin\hsodbc
                • 5. Re: ORA-28545: error diagnosed by Net8 when connecting to an agent error URGENT
                  680271
                  ERROR:
                  ORA-28545: error diagnosed by Net8 when connecting to an agent
                  NCRO: Failed to make RSLV connection
                  ORA-02063: preceding 2 lines from MSQL2K



                  pour cette erreur dans mon cas, il est due au mauvias chemin de orcale_home dans le fichier listener.ora en effet j'ai mis (ORACLE_HOME = d:\oracle\product\10.1.0\db_1) au lieu de (ORACLE_HOME = c:\oracle\product\10.1.0\db_1) (oracle est installé sur partition C: et nod D:)

                  extrait de listener.ora

                  LISTENER_MSQL2K =
                  (ADDRESS_LIST=
                  (ADDRESS=(PROTOCOL=tcp)(HOST=T100266681002)(PORT=1522))
                  (ADDRESS=(PROTOCOL=ipc)(KEY=PNPKEY)))

                  SID_LIST_LISTENER_MSQL2K =
                  (SID_LIST=
                  (SID_DESC=
                  (SID_NAME=MSQL2K)
                  (ORACLE_HOME = c:\oracle\product\10.1.0\db_1)
                  (PROGRAM=hsodbc)
                  )
                  )


                  extrait de tnsnames.ora
                  # tnsnames.ora Network Configuration File: C:\oracle\product\10.1.0\db_1\NETWORK\ADMIN\tnsnames.ora
                  # Generated by Oracle configuration tools.

                  PROD =
                  (DESCRIPTION =
                  (ADDRESS_LIST =
                  (ADDRESS = (PROTOCOL = TCP)(HOST = 128.0.0.152)(PORT = 1521))
                  )
                  (CONNECT_DATA =
                  (SERVER = DEDICATED)
                  (SERVICE_NAME = PROD)
                  )
                  )

                  ORCLMIROIR =
                  (DESCRIPTION =
                  (ADDRESS_LIST =
                  (ADDRESS = (PROTOCOL = TCP)(HOST = 172.16.15.20)(PORT = 1521))
                  )
                  (CONNECT_DATA =
                  (SERVER = DEDICATED)
                  (SERVICE_NAME = orcl)
                  )
                  )

                  ORCLS =
                  (DESCRIPTION =
                  (ADDRESS_LIST =
                  (ADDRESS = (PROTOCOL = TCP)(HOST = Server-OscarV4)(PORT = 1521))
                  )
                  (CONNECT_DATA =
                  (SID = ORCL)
                  )
                  )

                  ORCL =
                  (DESCRIPTION =
                  (ADDRESS_LIST =
                  (ADDRESS = (PROTOCOL = TCP)(HOST = T100266681002)(PORT = 1521))
                  )
                  (CONNECT_DATA =
                  (SERVER = DEDICATED)
                  (SERVICE_NAME = orcl)
                  )
                  )

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

                  # HETEROGENEOUS SERVICES ###############################################
                  MSQL2K =
                  (DESCRIPTION =
                  (ADDRESS_LIST =
                  (ADDRESS = (PROTOCOL = TCP)(HOST = T100266681002)(PORT = 1522 ))
                  )
                  (CONNECT_DATA =
                  (SID = MSQL2K)
                  )
                  (hs = ok)
                  )
                  • 6. Re: ORA-28545: error diagnosed by Net8 when connecting to an agent error URGENT
                    595834
                    I agree with previous poster. Error was also due to invalid value of ORACLE_HOME in listener.ora file.
                    • 7. Re: ORA-28545: error diagnosed by Net8 when connecting to an agent error URGENT
                      279225
                      Hi Ashok,

                      I have the same problm that you had over a year ago.

                      SQL> create database link MSSQL connect to "admin" identified by "XYZ" using 'MSSQL';

                      Database link created.

                      SQL> desc grocery.product@MSSQL;
                      ERROR:
                      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

                      How did you resolve it?

                      Thanks,

                      Dss
                      • 8. Re: ORA-28545: error diagnosed by Net8 when connecting to an agent error URGENT
                        532188
                        Hi.
                        Are you using 64-bit Oracle? If so, hsodbc program is 32-bit!

                        My case: SLES x86_64, Oracle 64-bit (I did changes for 10.1 and 10.2 also).
                        I was resolved this problem (to connect to PostgreSQL):

                        Set 32-bit environment at listener.ora:

                        (SID_DESC=
                        (SID_NAME = PostgreSQL)
                        (ORACLE_HOME = /opt/oracle/product/10.2/db)
                        (PROGRAM = hsodbc)
                        (ENVS = "LD_LIBRARY_PATH=/opt/oracle/product/10.2/db/lib32:/usr/lib:/opt/oracle/product/10.2/db/hs/lib32")
                        )

                        And hsodbc works!
                        • 9. Re: ORA-28545: error diagnosed by Net8 when connecting to an agent error URGENT
                          389236
                          !!! ATTENTION PLEASE !!!

                          I had the same error and I had fight with it 1 day until I discovered that it works if into listener.ora file the name of the listener is LISTENER.
                          I wanted to configure a HS connection with a PostgreSQL db

                          Example (listener.ora):

                          LISTENER =
                          (DESCRIPTION =
                          (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
                          (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
                          )

                          SID_LIST_LISTENER=
                          (SID_LIST=
                          (SID_DESC =
                          (SID_NAME = PLSExtProc)
                          (ORACLE_HOME = E:\oracle\product\10.2.0\db_1)
                          (PROGRAM = extproc)
                          )
                          (SID_DESC=
                          (SID_NAME=TEST)
                          (ORACLE_HOME=E:\oracle\product\10.2.0\db_1)
                          (PROGRAM=hsodbc)
                          )
                          )

                          Regards, CD.
                          • 10. Re: ORA-28545: error diagnosed by Net8 when connecting to an agent error URGENT
                            Kgronau-Oracle
                            you can name your listener as you want. You only need to specify then the listener name in the lsnrctl utility as the default name is LISTENER. You also need to make sure the port specified in your listener.ora isn't used by an already running listener process.

                            For example name it Listener_GTW11 with its corresponding SID_LIST_LISTENER_GTW11
                            Then you need to start the listener the first time with the command line: lsnrctl Listener_GTW11
                            the listener control utility now detects you don't have a service for it, creates the Listener_GTW11 service for you and starts the listener.
                            You can now set the service to autostart.
                            • 11. Re: ORA-28545: error diagnosed by Net8 when connecting to an agent error UR
                              749130
                              I also have teh same RA-28545: error diagnosed by Net8 when connecting to an agent error UR error.

                              I am trying to connect to the sql server database from oracle server

                              My SYSTEMDSN is in SQL SERVER name is EPROMIST

                              Listner.ora on sql server network/admin
                              # listener.ora Network Configuration File: D:\oracle\product\10.2.0\db_1\network\admin\listener.ora
                              # Generated by Oracle configuration tools.

                              LISTENEREPROMIST =
                              (DESCRIPTION_LIST =
                              (DESCRIPTION =
                              (ADDRESS = (PROTOCOL = TCP)(HOST = arabtec-epromis.arabtecuae.com)(PORT = 1521))
                              )
                              (DESCRIPTION =
                              (ADDRESS = (PROTOCOL = IPC)(KEY = PNPKEY))
                              )
                              )

                              CONNECT_TIMEOUT_LISTENER = 0

                              SID_LIST_LISTENEREPROMIST =
                              (SID_LIST =
                              (SID_DESC =
                              (SID_NAME = EpromisT)
                              (ORACLE_HOME = D:\oracle\product\10.2.0\db_1)
                              (PROGRAM = hsodbc)
                              )
                              (SID_DESC =
                              (GLOBAL_DBNAME = Oracle8)
                              (SID_NAME = ORCL)
                              )
                              )

                              initepromist.ora on SQL SERVER hs/admin
                              # This is a sample agent init file that contains the HS parameters that are
                              # needed for an ODBC Agent.

                              #
                              # HS init parameters
                              #
                              HS_FDS_CONNECT_INFO = EpromisT
                              HS_FDS_TRACE_LEVEL = off
                              #
                              # Environment variables required for the non-Oracle system
                              #
                              #set <envvar>=<value>



                              tnsnames.ora on Oracle Server network/admin
                              # tnsnames.ora Network Configuration File: E:\oracle\product\10.1.0\Db_1\NETWORK\ADMIN\tnsnames.ora
                              # Generated by Oracle configuration tools.

                              EPROMIST =
                              (DESCRIPTION =
                              (ADDRESS_LIST =
                              (ADDRESS = (PROTOCOL = TCP)(HOST = arabtec-epromis.arabtecuae.com)(PORT = 1521))
                              )
                              (CONNECT_DATA =
                              (SID = epromist)
                              )
                              (HS = OK)
                              )


                              database link on oracle server
                              create public database link epromist connect to scenario identified by scenario using 'epromist';

                              tnsping from oracle server
                              TNS Ping Utility for 32-bit Windows: Version 10.1.0.2.0 - Production on 26-JAN-2
                              010 16:14:26

                              Copyright (c) 1997, 2003, Oracle. All rights reserved.

                              Used parameter files:
                              E:\oracle\product\10.1.0\Db_1\network\admin\sqlnet.ora


                              Used TNSNAMES adapter to resolve the alias
                              Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)
                              (HOST = arabtec-epromis.arabtecuae.com)(PORT = 1521))) (CONNECT_DATA = (SID = ep
                              romist)) (HS = OK))
                              OK (20 msec)


                              the lsitner on the sql server is up and running.
                              please help me on this.

                              Thanks a million to all the help
                              • 12. Re: ORA-28545: error diagnosed by Net8 when connecting to an agent error UR
                                Mkirtley-Oracle
                                Hi,
                                Try changing the listener sid name from -

                                (SID_NAME = EpromisT)

                                to

                                (SID_NAME = epromist)

                                to match the init file initepromist.ora.

                                If that still fails then check the listener log for errors and also get an HSODBC debug trace if it is getting as far as starting the executable.
                                Add this to initepromist.ora -

                                HS_FDS_TRACE_LEVEL=debug

                                and try a select from a new SQLPLUS session.
                                Also, SQL*Server object names are case sensitive so you may need to change the create database link to -

                                create public database link epromist connect to "scenario" identified by "scenario" using 'epromist';

                                if the username and password were created in lower case on SQL*Server.

                                Regards,
                                Mike
                                • 13. Re: ORA-28545: error diagnosed by Net8 when connecting to an agent error UR
                                  749130
                                  hello Mike,

                                  Thanks a million billion times.

                                  It is working now.

                                  I changed both initepromist and listner files to have same case, then I modified the system dsn also to have the same case.

                                  Best Regards
                                  • 14. Re: ORA-28545: error diagnosed by Net8 when connecting to an agent error UR
                                    Mkirtley-Oracle
                                    Hi,
                                    That's good news. Thanks for letting me know it's now working.
                                    One further point HSODBC is now desupported and you should install and use the replacement product which is the 11g Database Gateway for ODBC.
                                    If you have access to My Oracle Support the following notes have details on the setup -

                                    Note.561033.1 How to Setup DG4ODBC on 64bit Unix OS (Linux, Solaris, AIX, HP-UX)
                                    Note.466228.1 How to Setup DG4ODBC on Linux x86 32bit
                                    Note.466225.1 How to Setup DG4ODBC (Oracle Database Gateway for ODBC) on Windows 32bit

                                    Regards,
                                    Mike
                                    1 2 Previous Next