1 2 3 Previous Next 31 Replies Latest reply: Sep 29, 2014 1:39 AM by Kam_oracle_apex RSS

    Error while Viewing SQL Server data from Oracle

    Kam_oracle_apex

      Dear Friends,

       

      I am using Oracle10g XE.

       

      I have made a connection to view or insert data in SQL Server Database from Oracle.

       

      I  have done all the things with the help of below link.

       

      http://www.databasejournal.com/features/oracle/article.php/3442661/Making-a-Connection-from-Oracle-to-SQL-Server.htm

       

      Everything worked fine. but when i run below query

       

      select "EmployeeNo" from hrtattendance@mysqlserverdsn

       

      it gives an error which is mentioned below

       

      ERROR at line 1:

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

      Unable to reteieve text of  NETWORK/NCR MESSAGE 65535

      ORA-02063: preceding 2 lines from MYSQLSERVERDSN

       

      Please help. I will be thankful.

       

      Regards,

        • 2. Re: Error while Viewing SQL Server data from Oracle
          Mkirtley-Oracle

          Hi,

            An ora-28545 error usually indicates a problem with the SQL*Net setup or the listener can't start he gateway executable.
          To follow up could you post the following files and information -

          - gateway listener.ora

          - tnsnames.ora used for the db link mysqlserverdsn

          - the create database link statement

          - the gateway init<sid>.ora

           

          Regards,

          Mike

          • 3. Re: Error while Viewing SQL Server data from Oracle
            Kam_oracle_apex

            Dear Mike,


            Please check below as u required.


            Listener.ora:

            location is     C:\oraclexe\app\oracle\product\10.2.0\server\NETWORK\ADMIN

             

            LISTENERMYSQLSERVERDSN =

            (ADDRESS_LIST=

                  (ADDRESS=(PROTOCOL=tcp)(HOST=localhost)(PORT=1522))

                  (ADDRESS=(PROTOCOL=ipc)(KEY=PNPKEY)))

             

             

            SID_LIST_LISTENERMYSQLSERVERDSN=

              (SID_LIST=

                  (SID_DESC=

                     (SID_NAME=MYSQLSERVERDSN)

                     (ORACLE_HOME = C:\oraclexe\app\oracle\product\10.2.0\server)

                     (PROGRAM=hsodbc)

                   )

                  )

            DEFAULT_SERVICE_LISTENER = (XE)

             

            tnsnames.ora

            location is     C:\oraclexe\app\oracle\product\10.2.0\server\NETWORK\ADMIN


            MYSQLSERVERDSN  =

              (DESCRIPTION=

                (ADDRESS=(PROTOCOL=tcp)(HOST=localhost)(PORT=1522))

                (CONNECT_DATA=(SID=MYSQLSERVERDSN))

                (HS=OK)

              )

             

            initMYSQLSERVERDSN.ora

            location is C:\oraclexe\app\oracle\product\10.2.0\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 = MYSQLSERVERDSN

            HS_FDS_TRACE_LEVEL = OFF

            #

            # Environment variables required for the non-Oracle system

            #

            #set <envvar>=<value>

             

             

            Regards,

            • 4. Re: Error while Viewing SQL Server data from Oracle
              Kgronau-Oracle

              Could you please open a command line (cmd) and execute the following commands and provide its output:

              1. C:\oraclexe\app\oracle\product\10.2.0\server\bin\hsodbc

              2. C:\oraclexe\app\oracle\product\10.2.0\server\bin\tnsping MYSQLSERVERDSN

              3. C:\oraclexe\app\oracle\product\10.2.0\server\bin\lsnrctl status LISTENERMYSQLSERVERDSN

               

              Thanks,

              Klaus

              • 5. Re: Error while Viewing SQL Server data from Oracle
                Mkirtley-Oracle

                Hi,

                  I thought I'd added this yesterday but the update isn't here.

                In the link you referred to they suggest using 'Windows NT' authentication for the System DSN but this will not work with HSODBC.

                If you chose this option then can you change it to 'SQL Server' authentication and try again ?


                Regards,

                Mike

                • 6. Re: Error while Viewing SQL Server data from Oracle
                  Kam_oracle_apex

                  Dear Klaus,

                   

                  Here u go.

                   

                  C:\>C:\oraclexe\app\oracle\product\10.2.0\server\bin\hsodbc

                   

                   

                   

                   

                   

                  Oracle Corporation --- TUESDAY   JUN 24 2014 16:28:20.146

                   

                   

                  Heterogeneous Agent Release 10.2.0.1.0 - Production  Built with

                     Driver for ODBC

                   

                   

                   

                   

                   

                   

                  C:\>C:\oraclexe\app\oracle\product\10.2.0\server\bin\tnsping MYSQLSERVERDSN

                   

                   

                  TNS Ping Utility for 32-bit Windows: Version 10.2.0.1.0 - Production on 24-JUN-2

                  014 16:28:33

                   

                   

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

                   

                   

                  Used parameter files:

                   

                  C:\oraclexe\app\oracle\product\10.2.0\server\network\admin\sqlnet.ora

                   

                   

                   

                   

                   

                  Used TNSNAMES adapter to resolve the alias

                  Attempting to contact (DESCRIPTION= (ADDRESS=(PROTOCOL=tcp)(HOST=localhost)(PORT

                  =1522)) (CONNECT_DATA=(SID=MYSQLSERVERDSN)) (HS=OK))

                  TNS-12541: TNS:no listener

                   

                   

                   

                  C:\>C:\oraclexe\app\oracle\product\10.2.0\server\bin\lsnrctl status LISTENERMYSQLSERVERDSN

                   

                   

                   

                  LSNRCTL for 32-bit Windows: Version 10.2.0.1.0 - Production on 24-JUN-2014 16:28

                  :48

                   

                   

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

                   

                   

                  Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=localhost)(PORT=1522))

                  TNS-12541: TNS:no listener

                  TNS-12560: TNS:protocol adapter error

                    TNS-00511: No listener

                     32-bit Windows Error: 61: Unknown error

                  Connecting to (ADDRESS=(PROTOCOL=ipc)(KEY=PNPKEY))

                  TNS-12541: TNS:no listener

                  TNS-12560: TNS:protocol adapter error

                    TNS-00511: No listener

                     32-bit Windows Error: 2: No such file or directory

                   

                   

                  C:\>

                   

                  Regards,

                  • 7. Re: Error while Viewing SQL Server data from Oracle
                    Mkirtley-Oracle

                    Hi,

                      So, the next question is what is returned by -

                     

                    C:\oraclexe\app\oracle\product\10.2.0\server\bin\lsnrctl start LISTENERMYSQLSERVERDSN

                     

                    It should show why the listener isn't starting.

                     

                    Regards,

                    Mike

                    • 8. Re: Error while Viewing SQL Server data from Oracle
                      Kam_oracle_apex

                      Dear Mike,

                       

                      Please see below: after this command c:\\lsnrctl start LISTENERMYSQLSERVERDSN

                       

                       

                      Microsoft Windows XP [Version 5.1.2600]

                      (C) Copyright 1985-2001 Microsoft Corp.

                       

                       

                      C:\Documents and Settings\amit>cd\

                       

                       

                      C:\>lsnrctl start LISTENERMYSQLSERVERDSN

                       

                       

                      LSNRCTL for 32-bit Windows: Version 10.2.0.1.0 - Production on 24-JUN-2014 16:58

                      :05

                       

                       

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

                       

                       

                      Starting tnslsnr: please wait...

                       

                       

                      TNSLSNR for 32-bit Windows: Version 10.2.0.1.0 - Production

                      System parameter file is C:\oraclexe\app\oracle\product\10.2.0\server\network\ad

                      min\listener.ora

                      Log messages written to C:\oraclexe\app\oracle\product\10.2.0\server\network\log

                      \listenermysqlserverdsn.log

                      Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.1)(PORT=1522)))

                      Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\PNPKEYipc))

                      )

                       

                       

                      Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=localhost)(PORT=1522))

                      STATUS of the LISTENER

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

                      Alias                     LISTENERMYSQLSERVERDSN

                      Version                   TNSLSNR for 32-bit Windows: Version 10.2.0.1.0 - Produ

                      ction

                      Start Date                24-JUN-2014 16:58:06

                      Uptime                    0 days 0 hr. 0 min. 1 sec

                      Trace Level               off

                      Security                  ON: Local OS Authentication

                      SNMP                      OFF

                      Listener Parameter File   C:\oraclexe\app\oracle\product\10.2.0\server\network\a

                      dmin\listener.ora

                      Listener Log File         C:\oraclexe\app\oracle\product\10.2.0\server\network\l

                      og\listenermysqlserverdsn.log

                      Listening Endpoints Summary...

                        (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.1)(PORT=1522)))

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

                      Services Summary...

                      Service "MYSQLSERVERDSN" has 1 instance(s).

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

                      .

                      The command completed successfully

                       

                       

                      C:\>

                      • 9. Re: Error while Viewing SQL Server data from Oracle
                        Mkirtley-Oracle

                        Hi,

                          Okay, that's good.  Does a tnsping and a select using the gateway database link now work ?  Remember my update about the authentication of the System DSN.

                         

                        Regards,

                        Mike

                        • 10. Re: Error while Viewing SQL Server data from Oracle
                          Kam_oracle_apex

                          Dear Mike,

                           

                          It does not work.

                           

                          Regards,

                          • 11. Re: Error while Viewing SQL Server data from Oracle
                            Kgronau-Oracle

                            Could you please clarify what does not work - the tnsping or the select?

                            Please provide the output of the tnsping command: C:\oraclexe\app\oracle\product\10.2.0\server\bin\tnsping MYSQLSERVERDSN

                            - Klaus

                            • 12. Re: Error while Viewing SQL Server data from Oracle
                              Kam_oracle_apex

                              Dear Mike,

                               

                              Please see below.

                               

                               

                               

                              C:\>tnsping MYSQLSERVERDSN

                               

                               

                              TNS Ping Utility for 32-bit Windows: Version 10.2.0.1.0 - Production on 26-JUN-2

                              014 12:50:15

                               

                               

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

                               

                               

                              Used parameter files:

                              C:\oraclexe\app\oracle\product\10.2.0\server\network\admin\sqlnet.ora

                               

                               

                               

                               

                              Used TNSNAMES adapter to resolve the alias

                              Attempting to contact (DESCRIPTION= (ADDRESS=(PROTOCOL=tcp)(HOST=localhost)(PORT

                              =1522)) (CONNECT_DATA=(SID=MYSQLSERVERDSN)) (HS=OK))

                              TNS-12541: TNS:no listener

                               

                               

                              C:\>

                               

                              Regards,

                              • 13. Re: Error while Viewing SQL Server data from Oracle
                                Kam_oracle_apex

                                Dear Klaus,

                                 

                                Please see below.

                                 

                                 

                                 

                                C:\>tnsping MYSQLSERVERDSN

                                 

                                 

                                TNS Ping Utility for 32-bit Windows: Version 10.2.0.1.0 - Production on 26-JUN-2

                                014 12:50:15

                                 

                                 

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

                                 

                                 

                                Used parameter files:

                                C:\oraclexe\app\oracle\product\10.2.0\server\network\admin\sqlnet.ora

                                 

                                 

                                 

                                 

                                Used TNSNAMES adapter to resolve the alias

                                Attempting to contact (DESCRIPTION= (ADDRESS=(PROTOCOL=tcp)(HOST=localhost)(PORT

                                =1522)) (CONNECT_DATA=(SID=MYSQLSERVERDSN)) (HS=OK))

                                TNS-12541: TNS:no listener

                                 

                                 

                                C:\>

                                 

                                Regards,

                                • 14. Re: Error while Viewing SQL Server data from Oracle
                                  Kgronau-Oracle

                                  Well, the error message is clear - your listener is not up and running. Check again with C:\oraclexe\app\oracle\product\10.2.0\server\bin\lsnrctl status LISTENERMYSQLSERVERDSN and if it is not there, then start it using C:\oraclexe\app\oracle\product\10.2.0\server\bin\lsnrctl start LISTENERMYSQLSERVERDSN.

                                  - Klaus

                                  1 2 3 Previous Next