This discussion is archived
1 2 Previous Next 19 Replies Latest reply: Aug 16, 2012 5:36 AM by Dharma_ RSS

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

Dharma_ Newbie
Currently Being Moderated
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 Expert
    Currently Being Moderated
    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_ Newbie
    Currently Being Moderated
    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 Expert
    Currently Being Moderated
    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_ Newbie
    Currently Being Moderated
    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 Expert
    Currently Being Moderated
    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_ Newbie
    Currently Being Moderated
    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_ Newbie
    Currently Being Moderated
    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 Expert
    Currently Being Moderated
    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_ Newbie
    Currently Being Moderated
    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 Expert
    Currently Being Moderated
    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_ Newbie
    Currently Being Moderated
    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_ Newbie
    Currently Being Moderated
    Hai mike,

    Waiting for your replay

    Regards
    Dharma
  • 13. Re: [S1T00][unixODBC][Microsoft][SQL Server Native Client 11.0]Login timeout ex
    mkirtley-Oracle Expert
    Currently Being Moderated
    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_ Newbie
    Currently Being Moderated
    Dear mike,

    Thanks for your replay

    Regards

    Dharma
1 2 Previous Next

Legend

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