1 2 Previous Next 19 Replies Latest reply: Aug 16, 2012 7:36 AM by Dharma_ RSS

    [S1T00][unixODBC][Microsoft][SQL Server Native Client 11.0]Login timeout ex

    Dharma_
      Dear all,

      Please guide me to solve below error,

      I have creeated dblink for oracle to sql server 2005,I have configured everying,and try isql test,it through below error,


      [oradev@testebs bin]$ isql -v dblink4sqlserver
      [S1T00][unixODBC][Microsoft][SQL Server Native Client 11.0]Login timeout expired
      [08001][unixODBC][Microsoft][SQL Server Native Client 11.0]A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online.
      [08001][unixODBC][Microsoft][SQL Server Native Client 11.0]TCP Provider: Error code 0x2726
      [ISQL]ERROR: Could not SQLConnect


      **********************HS trace file below:***********************

      HOSGIP for "HS_FDS_TRACE_LEVEL" returned "ON"
      HOSGIP for "HS_FDS_SHAREABLE_NAME" returned "/usr/local/lib/libodbc.so"
      HOSGIP for "HS_OPEN_CURSORS" returned "50"
      HOSGIP for "HS_FDS_FETCH_ROWS" returned "100"
      HOSGIP for "HS_LONG_PIECE_TRANSFER_SIZE" returned "65536"
      HOSGIP for "HS_NLS_NUMERIC_CHARACTER" returned ".,"
      HOSGIP for "HS_FDS_RECOVERY_ACCOUNT" returned "RECOVER"
      HOSGIP for "HS_FDS_TRANSACTION_LOG" returned ""HS_TRANSACTION_LOG""
      HOSGIP for "HS_FDS_TIMESTAMP_AS_DATE" returned "TRUE"
      HOSGIP for "HS_FDS_CHARACTER_SEMANTICS" returned "FALSE"
      HOSGIP for "HS_FDS_MAP_NCHAR" returned "TRUE"
      HOSGIP for "HS_FDS_RESULT_SET_SUPPORT" returned "FALSE"
      HOSGIP for "HS_FDS_PROC_IS_FUNC" returned "FALSE"
      HOSGIP for "HS_FDS_REPORT_REAL_AS_DOUBLE" returned "FALSE"
      using sa as default value for "HS_FDS_DEFAULT_OWNER"
      HOSGIP for "HS_SQL_HANDLE_STMT_REUSE" returned "FALSE"
      ##>Connect Parameters (len=40)<##
      ## DSN=dblink4sqlserver;
      #! UID=sa;
      #! PWD=*

      **************************Alert log Error Information:**************************


      HS: Created new FDS instance definition in server DD
      HS: Instance id = 44, instance name = backoff (class ODBC11.1.0.7.0_0006)
      HS: Lost RPC connection to remote Agent...
      HS: ... Agent SID = (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.2.14)(port=1526))(CONNECT_DATA=(SID=backoff))), NCR status = -2147385341


      Please refer above error,and guide me

      Regards
      Dharma
        • 1. Re: [S1T00][unixODBC][Microsoft][SQL Server Native Client 11.0]Login timeout ex
          Mkirtley-Oracle
          Dharma,
          If the isql connection doesn't work then the DG4ODBC conenction will never work either.
          There is a problem with the basic ODBC setup which needs to be resolved.
          You should really follow this up with the supplier of the ODBC driver as there is no Oracle software involved in the ISQL connection.
          However, post the odbc.ini file used for the setup and we may be able to help.

          As the error message says - there is a problem with the conenction to the SQL*Server database so you need to check the details in the odbc.ini file are correct.

          Regards,
          Mike
          • 2. Re: [S1T00][unixODBC][Microsoft][SQL Server Native Client 11.0]Login timeout ex
            Dharma_
            Hai mike,

            thanks for replay mike,

            Now isql error resolved,but i cant query any sql serve table uning dblink,please refer below error,

            [oradev@testebs bin]$ isql -v dblink4sqlserver sa nilgiris
            ---------------------------------------
            | Connected! |
            | |
            | sql-statement |
            | help [tablename] |
            | quit |
            | |
            ---------------------------------------
            SQL> desc store@ORACLE_SQL;
            [37000][Microsoft][SQL Server Native Client 11.0][SQL Server]Statement(s) could not be prepared.
            [37000][Microsoft][SQL Server Native Client 11.0][SQL Server]Incorrect syntax near the keyword 'desc'.
            [ISQL]ERROR: Could not SQLExecute
            SQL> select *from store@ORACLE_SQL;
            [S0002][Microsoft][SQL Server Native Client 11.0][SQL Server]Invalid object name 'store@ORACLE_SQL'.
            [37000][Microsoft][SQL Server Native Client 11.0][SQL Server]Statement(s) could not be prepared.
            [ISQL]ERROR: Could not SQLExecute


            Above error mention invalid object name,but i checked that at sqlserver,that table will be there.

            And i query Sqlplus,it will show different error,

            Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production
            With the Partitioning, OLAP, Data Mining and Real Application Testing options

            SQL> select *from store@ORACLE_SQL;
            select *from store@ORACLE_SQL
            *
            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=192.168.2.14)(port=1526))(CONNECT_
            DATA=(SID=BACKOFF)))
            ORA-02063: preceding line from ORACLE_SQL
            Process ID: 14054
            Session ID: 2900 Serial number: 10756


            Please guide me to solve above error

            Regards
            Dharma
            • 3. Re: [S1T00][unixODBC][Microsoft][SQL Server Native Client 11.0]Login timeout ex
              Mkirtley-Oracle
              Hi Dharma,
              From ISQL are you trying to use an Oracle database link -

              SQL> desc store@ORACLE_SQL;
              [37000][Microsoft][SQL Server Native Client 11.0][SQL Server]Statement(s) could not be prepared.
              [37000][Microsoft][SQL Server Native Client 11.0][SQL Server]Incorrect syntax near the keyword 'desc'.
              [ISQL]ERROR: Could not SQLExecute
              SQL> select *from store@ORACLE_SQL;

              If so, that isn't supported.
              From isql you should be able to do -

              describe store
              select * from store ;

              as you are already connected to the SQL*Server database.
              SQL*Server names are case sensitive so you may need to do -

              select * from "store" ;

              The SQLPLUS ORA-28511 error can have different causes so we need to see the configuration files -

              - gateway listener.ora
              - gateway initBACKOFF.ora
              - tnsnames.ora
              - create database link statement (hash out the password)

              and also a 255 level gateway trace.
              From the errors the gateway SID looks to be BACKOFF.

              In initBACKOFF.ora set the following -

              HS_FDS_TRACE_LEVEL=255

              and run a select from a new SQLPLUS session -

              select * from "store"@oracle_sql ;

              then end the session.
              The trace will be in the gateway $ORACLE_HOME/hs/log - on Linux/Unix. On Windows it is in $ORACLE_HOME/hs/trace.

              If the trace is large either upload to somewhere offsite where it is accessible or post the later section which should show some errors.

              Regards,
              Mike
              • 4. Re: [S1T00][unixODBC][Microsoft][SQL Server Native Client 11.0]Login timeout ex
                Dharma_
                Hai mike

                Thank for replay,

                Now i got these error

                SQL> select *from "store"@ORACLE_SQL;
                select *from "store"@ORACLE_SQL
                *
                ERROR at line 1:
                ORA-12154: TNS:could not resolve the connect identifier specified


                1)Gateway listener.ora

                SID_LIST_DEV =
                (SID_LIST =
                (SID_DESC =
                (ORACLE_HOME= /Oracle/CRP/DEV/db/tech_st/11.1.0)
                (SID_NAME = DEV)
                )
                (SID_DESC=
                (SID_NAME = NILGDB2)
                (ORACLE_HOME= /Oracle/CRP/DEV/db/tech_st/11.1.0/)
                (PROGRAM = dg4odbc)
                (ENVS=LD_LIBRARY_PATH = /Oracle/DBlink_RPM/ODBC_Driver/odbc_cli/clidriver/lib:/Oracle/CRP/DEV/db/tech_st/11.1.0/lib)
                )
                (SID_DESC=
                (SID_NAME=backoff)
                (ORACLE_HOME=/Oracle/CRP/DEV/db/tech_st/11.1.0/)
                (PROGRAM=dg4odbc)
                (ENVS=LD_LIBRARY_PATH=/Oracle/DBlink_RPM/ODBC_Driver/sqlncli-11.0.1790.0/lib:/Oracle/CRP/DEV/db/tech_st/11.1.0/lib)
                )
                )


                2)tnsnames.ora


                backoff=
                (DESCRIPTION =
                (ADDRESS=(PROTOCOL=TCP)(HOST=192.168.2.14)(port=1526))
                (CONNECT_DATA =
                (SID = backoff)
                )
                (HS=OK)
                )


                3)initBACKOFF.ora

                HS_FDS_CONNECT_INFO = dblink4sqlserver
                HS_FDS_TRACE_LEVEL=255
                HS_FDS_TRACE_FILE = /$ORACLE_HOME/hs/log/hstrace.txt
                #HS_LANGUAGE=AMERICAN_AMERICA.WE8ISO8859P1
                HS_FDS_SHAREABLE_NAME = /usr/local/lib/libodbc.so
                HS_NLS_NCHAR = AMERICAN_AMERICA.US7ASCII
                HS_LANGUAGE = AMERICAN_AMERICA.US7ASCII
                HS_FDS_CONNECT_PROPERTIES='120'


                set ODBCINI=/usr/local/etc/odbc.ini

                4)Dblink

                Create public database link ORACLE_SQL connect to "erp" identified by "123456789" using 'backoff';

                Please refer above error,and trace file not generated

                Regards
                Dharma
                • 5. Re: [S1T00][unixODBC][Microsoft][SQL Server Native Client 11.0]Login timeout ex
                  Mkirtley-Oracle
                  Dharma,
                  The TNS-12154 (ORA-12154) means that SQL*Net could not find the alias specified for a connection in the TNSNAMES.ORA file or other naming adapter.
                  In tnsnames.ora and listener.ora you refer to the gateway sid as 'backoff' but the init file is called initBACKOFF.ora.
                  Can you change the name to upper case in both files, stop and start the lsitener and try again ?

                  Also, you have given the full listener details but I assume 192.168.2.14 is the hsot where the listener is running ?

                  You won't get a gateway trace file because you are not getting as far as starting the gateway executable.
                  One other point, you should comment out the like -

                  HS_FDS_TRACE_FILE = /$ORACLE_HOME/hs/log/hstrace.txt

                  and leave the trace name to the default name, which will be backoff_agt_xxxx.trc - where xxxx is the gateway pid number and will change each time you start a gateway executable.

                  Regards,
                  Mike
                  • 6. Re: [S1T00][unixODBC][Microsoft][SQL Server Native Client 11.0]Login timeout ex
                    Dharma_
                    Dear Mike,

                    I have changed previously as you mention,but li got different error,


                    SQL> create public database link ORACLE_SQL connect to "sa" identified by "nilgiris" using 'BACKOFF';

                    Database link created.

                    SQL> select *from "store"@ORACLE_SQL;
                    select *from "store"@ORACLE_SQL
                    *
                    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=192.168.2.14)(port=1526))(CONNECT_
                    DATA=(SID=BACKOFF)))
                    ORA-02063: preceding line from ORACLE_SQL
                    Process ID: 7584
                    Session ID: 2843 Serial number: 1149


                    Please refer my configuration below,



                    *******sqlnet.ora***********

                    NAMES.DIRECTORY_PATH=(TNSNAMES, ONAMES, HOSTNAME)
                    SQLNET.EXPIRE_TIME= 10
                    SQLNET.INBOUND_CONNECT_TIMEOUT= 120
                    DIAG_ADR_ENABLED=ON
                    ADR_BASE=/Oracle/CRP/DEV/db/tech_st/11.1.0/admin/DEV_testebs
                    SEC_USER_AUDIT_ACTION_BANNER = /Oracle/CRP/DEV/db/tech_st/11.1.0/appsutil/template/txkDBSecUserAuditActionBanner.txt


                    IFILE=/Oracle/CRP/DEV/db/tech_st/11.1.0/network/admin/DEV_testebs/sqlnet_ifile.ora


                    ***********listener.ora******************

                    DEV =
                    (DESCRIPTION_LIST =
                    (DESCRIPTION =
                    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.2.14)(PORT = 1526))
                    )
                    )

                    SID_LIST_DEV =
                    (SID_LIST =
                    (SID_DESC =
                    (ORACLE_HOME= /Oracle/CRP/DEV/db/tech_st/11.1.0)
                    (SID_NAME = DEV)
                    )
                    (SID_DESC=
                    (SID_NAME = NILGDB2)
                    (ORACLE_HOME= /Oracle/CRP/DEV/db/tech_st/11.1.0/)
                    (PROGRAM = dg4odbc)
                    (ENVS=LD_LIBRARY_PATH = /Oracle/DBlink_RPM/ODBC_Driver/odbc_cli/clidriver/lib:/Oracle/CRP/DEV/db/tech_st/11.1.0/lib)
                    )
                    (SID_DESC=
                    (SID_NAME=BACKOFF)
                    (ORACLE_HOME=/Oracle/CRP/DEV/db/tech_st/11.1.0/)
                    (PROGRAM=dg4odbc)
                    (ENVS=LD_LIBRARY_PATH=/Oracle/DBlink_RPM/ODBC_Driver/sqlncli-11.0.1790.0/lib:/Oracle/CRP/DEV/db/tech_st/11.1.0/lib)
                    )
                    )


                    )


                    *************tnsnames.ora*********************

                    BACKOFF=
                    (DESCRIPTION =
                    (ADDRESS=(PROTOCOL=TCP)(HOST=192.168.2.14)(port=1526))
                    (CONNECT_DATA =
                    (SID = BACKOFF)
                    )
                    (HS=OK)
                    )

                    **************initBACKOFF.ora*******************

                    HS_FDS_CONNECT_INFO = dblink4sqlserver
                    HS_FDS_TRACE_LEVEL=255
                    HS_FDS_TRACE_FILE = /$ORACLE_HOME/hs/log/hstrace.txt
                    #HS_LANGUAGE=AMERICAN_AMERICA.WE8ISO8859P1
                    HS_FDS_SHAREABLE_NAME = /usr/local/lib/libodbc.so
                    HS_NLS_NCHAR = AMERICAN_AMERICA.US7ASCII
                    HS_LANGUAGE = AMERICAN_AMERICA.US7ASCII
                    HS_FDS_CONNECT_PROPERTIES='120'


                    set ODBCINI=/usr/local/etc/odbc.ini

                    *************************************************


                    Regards

                    Dharma
                    • 7. Re: [S1T00][unixODBC][Microsoft][SQL Server Native Client 11.0]Login timeout ex
                      Dharma_
                      Dear mike,

                      Please refer these trace file also,


                      Exiting hgolofn, rc=0 at 2012/08/13-19:14:47
                      HOSGIP for "HS_OPEN_CURSORS" returned "50"
                      HOSGIP for "HS_FDS_FETCH_ROWS" returned "100"
                      HOSGIP for "HS_LONG_PIECE_TRANSFER_SIZE" returned "65536"
                      HOSGIP for "HS_NLS_NUMERIC_CHARACTER" returned ".,"
                      Exiting hgoinit, rc=0 at 2012/08/13-19:14:47
                      hostmstr: 0: HOA After hoainit
                      hostmstr: 0: HOA Before hoalgon
                      Entered hgolgon at 2012/08/13-19:14:47
                      reco:0, name:sa, tflag:0
                      Entered hgosuec at 2012/08/13-19:14:47
                      Exiting hgosuec, rc=0 at 2012/08/13-19:14:47
                      HOSGIP for "HS_FDS_RECOVERY_ACCOUNT" returned "RECOVER"
                      HOSGIP for "HS_FDS_TRANSACTION_LOG" returned ""HS_TRANSACTION_LOG""
                      HOSGIP for "HS_FDS_TIMESTAMP_AS_DATE" returned "TRUE"
                      HOSGIP for "HS_FDS_CHARACTER_SEMANTICS" returned "FALSE"
                      HOSGIP for "HS_FDS_MAP_NCHAR" returned "TRUE"
                      HOSGIP for "HS_FDS_RESULT_SET_SUPPORT" returned "FALSE"
                      HOSGIP for "HS_FDS_PROC_IS_FUNC" returned "FALSE"
                      HOSGIP for "HS_FDS_REPORT_REAL_AS_DOUBLE" returned "FALSE"
                      using sa as default value for "HS_FDS_DEFAULT_OWNER"
                      HOSGIP for "HS_SQL_HANDLE_STMT_REUSE" returned "FALSE"
                      Entered hgocont at 2012/08/13-19:14:47
                      HS_FDS_CONNECT_INFO = "dblink4sqlserver"
                      RC=-1 from HOSGIP for "HS_FDS_CONNECT_STRING"
                      Entered hgogenconstr at 2012/08/13-19:14:47
                      dsn:dblink4sqlserver, name:sa
                      optn:
                      ##>Connect Parameters (len=40)<##
                      ## DSN=dblink4sqlserver;
                      #! UID=sa;
                      #! PWD=*
                      Exiting hgogenconstr, rc=0 at 2012/08/13-19:14:47


                      Regards
                      Dharma
                      • 8. Re: [S1T00][unixODBC][Microsoft][SQL Server Native Client 11.0]Login timeout ex
                        Mkirtley-Oracle
                        Hi Dharma,
                        In the gateway init file can you remove the parameter -

                        HS_FDS_CONNECT_PROPERTIES='120'

                        and change the parameter -

                        HS_NLS_NCHAR = AMERICAN_AMERICA.US7ASCII

                        to

                        HS_NLS_NCHAR = UCS2

                        and try from a new SLQPLUS session.

                        Regards,
                        Mike
                        • 9. Re: [S1T00][unixODBC][Microsoft][SQL Server Native Client 11.0]Login timeout ex
                          Dharma_
                          Dear Mike,

                          Now error is changed,

                          SQL> select *from "store"@ORACLE_SQL;
                          select *from "store"@ORACLE_SQL
                          *
                          ERROR at line 1:
                          ORA-28500: connection from ORACLE to a non-Oracle system returned this message:
                          [



                          Regards
                          Dharma
                          • 10. Re: [S1T00][unixODBC][Microsoft][SQL Server Native Client 11.0]Login timeout ex
                            Mkirtley-Oracle
                            Dharma,
                            It looks like we are nearly there and you are probably hitting the problem is this note -

                            Error Ora-28500 and Sqlstate I Issuing Selects From a Unicode Oracle RDBMS With Dg4odbc To Mysql or SQL*Server (Doc ID 756186.1)

                            To check add the following to the gateway init file -

                            HS_LANGUAGE=AMERICAN_AMERICA.WE8ISO8859P1

                            If this doesn't work then send the output from -

                            select * from nls_database_parameters ;

                            and a 255 gateway trace.

                            Regards,
                            Mike
                            • 11. Re: [S1T00][unixODBC][Microsoft][SQL Server Native Client 11.0]Login timeout ex
                              Dharma_
                              Dear Mike,

                              As per your previous post,i have changed initBACKOFF.ora file parameter
                              HS_LANGUAGE=AMERICAN_AMERICA.WE8ISO8859P1.

                              Now error has been changed,previously i posted same error,


                              Please refer trace file below.

                              HOSGIP for "HS_FDS_FETCH_ROWS" returned "100"
                              HOSGIP for "HS_LONG_PIECE_TRANSFER_SIZE" returned "65536"
                              HOSGIP for "HS_NLS_NUMERIC_CHARACTER" returned ".,"
                              Exiting hgoinit, rc=0 at 2012/08/14-00:00:52
                              hostmstr: 0: HOA After hoainit
                              hostmstr: 0: HOA Before hoalgon
                              Entered hgolgon at 2012/08/14-00:00:52
                              reco:0, name:sa, tflag:0
                              Entered hgosuec at 2012/08/14-00:00:52
                              Exiting hgosuec, rc=0 at 2012/08/14-00:00:52
                              HOSGIP for "HS_FDS_RECOVERY_ACCOUNT" returned "RECOVER"
                              HOSGIP for "HS_FDS_TRANSACTION_LOG" returned ""HS_TRANSACTION_LOG""
                              HOSGIP for "HS_FDS_TIMESTAMP_AS_DATE" returned "TRUE"
                              HOSGIP for "HS_FDS_CHARACTER_SEMANTICS" returned "FALSE"
                              HOSGIP for "HS_FDS_MAP_NCHAR" returned "TRUE"
                              HOSGIP for "HS_FDS_RESULT_SET_SUPPORT" returned "FALSE"
                              HOSGIP for "HS_FDS_PROC_IS_FUNC" returned "FALSE"
                              HOSGIP for "HS_FDS_REPORT_REAL_AS_DOUBLE" returned "FALSE"
                              using sa as default value for "HS_FDS_DEFAULT_OWNER"
                              HOSGIP for "HS_SQL_HANDLE_STMT_REUSE" returned "FALSE"
                              Entered hgocont at 2012/08/14-00:00:52
                              HS_FDS_CONNECT_INFO = "dblink4sqlserver"
                              RC=-1 from HOSGIP for "HS_FDS_CONNECT_STRING"
                              Entered hgogenconstr at 2012/08/14-00:00:52
                              dsn:dblink4sqlserver, name:sa
                              optn:
                              ##>Connect Parameters (len=40)<##
                              ## DSN=dblink4sqlserver;
                              #! UID=sa;
                              #! PWD=*
                              Exiting hgogenconstr, rc=0 at 2012/08/14-00:00:52


                              select * from nls_database_parameters ;


                              PARAMETER VALUE
                              ------------------------------ ------------------------------------------------------------------------------------------------------------------------
                              NLS_LANGUAGE AMERICAN
                              NLS_TERRITORY AMERICA
                              NLS_CURRENCY $
                              NLS_ISO_CURRENCY AMERICA
                              NLS_NUMERIC_CHARACTERS .,
                              NLS_CHARACTERSET UTF8
                              NLS_CALENDAR GREGORIAN
                              NLS_DATE_FORMAT DD-MON-RR
                              NLS_DATE_LANGUAGE AMERICAN
                              NLS_SORT BINARY
                              NLS_TIME_FORMAT HH.MI.SSXFF AM

                              PARAMETER VALUE
                              ------------------------------ ------------------------------------------------------------------------------------------------------------------------
                              NLS_TIMESTAMP_FORMAT DD-MON-RR HH.MI.SSXFF AM
                              NLS_NCHAR_CHARACTERSET AL16UTF16
                              NLS_TIME_TZ_FORMAT HH.MI.SSXFF AM TZH:TZM
                              NLS_TIMESTAMP_TZ_FORMAT DD-MON-RR HH.MI.SSXFF AM TZH:TZM
                              NLS_DUAL_CURRENCY $
                              NLS_COMP BINARY
                              NLS_LENGTH_SEMANTICS BYTE
                              NLS_NCHAR_CONV_EXCP FALSE
                              NLS_RDBMS_VERSION 11.1.0.7.0


                              Please Refer above


                              Regards
                              Dharma
                              • 12. Re: [S1T00][unixODBC][Microsoft][SQL Server Native Client 11.0]Login timeout ex
                                Dharma_
                                Hai mike,

                                Waiting for your replay

                                Regards
                                Dharma
                                • 13. Re: [S1T00][unixODBC][Microsoft][SQL Server Native Client 11.0]Login timeout ex
                                  Mkirtley-Oracle
                                  Dharma,
                                  I'm checking the information you have sent and will get back.

                                  Regards,
                                  Mike
                                  • 14. Re: [S1T00][unixODBC][Microsoft][SQL Server Native Client 11.0]Login timeout ex
                                    Dharma_
                                    Dear mike,

                                    Thanks for your replay

                                    Regards

                                    Dharma
                                    1 2 Previous Next