This discussion is archived
4 Replies Latest reply: Aug 27, 2012 12:41 PM by Richard Harrison . RSS

ORA-28500: connection from ORACLE to a non-Oracle system returned this

user13707876 Newbie
Currently Being Moderated
Hi ALL<


Please assist i am doing heterogeneous connectivity as oracle to sql server the below error encounterd after created db link.


SQL> select * from SqlTest@jadoo;
select * from SqlTest@jadoo
*
ERROR at line 1:
ORA-28500: connection from ORACLE to a non-Oracle system returned this message:
ORA-28541: Error in HS init file on line 20.
ORA-02063: preceding 2 lines from JADOO
  • 1. Re: ORA-28500: connection from ORACLE to a non-Oracle system returned this
    user13707876 Newbie
    Currently Being Moderated
    Hi,

    my dg4odbc.ora is as below:
    --------------

    $ cat initdg4odbc.ora
    # This is a sample agent init file that contains the HS parameters that are
    # needed for the Database Gateway for ODBC

    #
    # HS init parameters
    #
    HS_FDS_CONNECT_INFO = mssqlserver
    HS_FDS_TRACE_LEVEL = off
    HS_FDS_SHAREABLE_NAME = /usr/opt/ibm/WSII/odbc/ddl/lib/FRmsss21.so

    #
    # ODBC specific environment variables
    #
    set ODBCINI=/usr/opt/ibm/WSII/odbc/ddl/odbc.ini


    #
    # Environment variables required for the non-Oracle system
    #
    set <envvar>=<value>
    $
    ----------------
    MY odbc.ini file is as :
    ----------------------------------------
    $ cat odbc.ini
    [ODBC Data Sources]
    DB2 Wire Protocol=DataDirect 5.1 DB2 Wire Protocol
    Informix Wire Protocol=DataDirect 5.1 Informix Wire Protocol
    Oracle Wire Protocol=DataDirect 5.0 Oracle Wire Protocol
    Oracle=DataDirect 5.1 Oracle
    SQLServer Wire Protocol=DataDirect 5.1 SQL Server Wire Protocol
    Sybase Wire Protocol=DataDirect 5.1 Sybase Wire Protocol
    mssqlserver=MS SQL Server 2008

    [DB2 Wire Protocol]
    Driver=/home2/alesio/odbc64v51/lib/dddb221.so
    Description=DataDirect 5.1 DB2 Wire Protocol
    AddStringToCreateTable=
    AlternateID=
    AlternateServers=
    ApplicationUsingThreads=1
    CatalogSchema=
    CharsetFor65535=0
    #Collection applies to OS/390 and AS/400 only
    Collection=
    ConnectionRetryCount=0
    ConnectionRetryDelay=3
    #Database applies to DB2 UDB only
    Database=<database_name>
    DynamicSections=200
    GrantAuthid=PUBLIC
    GrantExecute=1
    IpAddress=<DB2_server_host>
    LoadBalancing=0
    #Location applies to OS/390 and AS/400 only
    Location=<location_name>
    LogonID=
    Password=
    PackageOwner=
    ReportCodePageConversionErrors=0
    SecurityMechanism=0
    TcpPort=<DB2_server_port>
    UseCurrentSchema=1
    WithHold=1

    [Informix Wire Protocol]
    Driver=/home2/alesio/odbc64v51/lib/ddifcl21.so
    Description=DataDirect 5.1 Informix Wire Protocol
    AlternateServers=
    ApplicationUsingThreads=1
    CancelDetectInterval=0
    ConnectionRetryCount=0
    ConnectionRetryDelay=3
    Database=<database_name>
    HostName=<Informix_host>
    LoadBalancing=0
    LogonID=
    Password=
    PortNumber=<Informix_server_port>
    ReportCodePageConversionErrors=0
    ServerName=<Informix_server>
    TrimBlankFromIndexName=1

    [Oracle Wire Protocol]
    Driver=/home2/alesio/odbc64v51/lib/ddora21.so
    Description=DataDirect 5.1 Oracle Wire Protocol
    AlternateServers=
    ApplicationUsingThreads=1
    ArraySize=60000
    CachedCursorLimit=32
    CachedDescLimit=0
    CatalogIncludesSynonyms=1
    CatalogOptions=0
    ConnectionRetryCount=0
    ConnectionRetryDelay=3
    DefaultLongDataBuffLen=1024
    DescribeAtPrepare=0
    EnableDescribeParam=0
    EnableNcharSupport=0
    EnableScrollableCursors=1
    EnableStaticCursorsForLongData=0
    EnableTimestampWithTimeZone=0
    HostName=<Oracle_server>
    LoadBalancing=0
    LocalTimeZoneOffset=
    LockTimeOut=-1
    LogonID=
    Password=
    PortNumber=1521
    ProcedureRetResults=0
    ReportCodePageConversionErrors=0
    ServiceType=0
    ServiceName=
    SID=<Oracle_SID>
    TimeEscapeMapping=0
    UseCurrentSchema=1

    [Oracle]
    Driver=/home2/alesio/odbc64v51/lib/ddor821.so
    Description=DataDirect 5.1 Oracle
    AlternateServers=
    ApplicationUsingThreads=1
    ArraySize=60000
    CatalogIncludesSynonyms=1
    CatalogOptions=0
    ClientVersion=9iR2
    ConnectionRetryCount=0
    ConnectionRetryDelay=3
    DefaultLongDataBuffLen=1024
    DescribeAtPrepare=0
    EnableDescribeParam=0
    EnableNcharSupport=0
    EnableScrollableCursors=1
    EnableStaticCursorsForLongData=0
    EnableTimestampWithTimeZone=0
    LoadBalancing=0
    LocalTimeZoneOffset=
    LockTimeOut=-1
    LogonID=
    OptimizeLongPerformance=0
    Password=
    ProcedureRetResults=0
    ReportCodePageConversionErrors=0
    ServerName=<Oracle_server>
    TimestampEscapeMapping=0
    UseCurrentSchema=1

    [SQLServer Wire Protocol]
    Driver=/home2/alesio/odbc64v51/lib/ddmsss21.so
    Description=DataDirect 5.1 SQL Server Wire Protocol
    Address=<SQLServer_host, SQLServer_server_port>
    AlternateServers=
    AnsiNPW=Yes
    ConnectionRetryCount=0
    ConnectionRetryDelay=3
    Database=<database_name>
    LoadBalancing=0
    LogonID=
    Password=
    QuotedId=No
    ReportCodePageConversionErrors=0

    [Sybase Wire Protocol]
    Driver=/home2/alesio/odbc64v51/lib/ddase21.so
    Description=DataDirect 5.1 Sybase Wire Protocol
    AlternateServers=
    ApplicationName=
    ApplicationUsingThreads=1
    ArraySize=50
    Charset=
    ConnectionRetryCount=0
    ConnectionRetryDelay=3
    CursorCacheSize=1
    Database=<database_name>
    DefaultLongDataBuffLen=1024
    EnableDescribeParam=0
    EnableQuotedIdentifiers=0
    InitializationString=
    Language=
    LoadBalancing=0
    LogonID=
    NetworkAddress=<Sybase_host, Sybase_server_port>
    OptimizePrepare=1
    PacketSize=0
    Password=
    RaiseErrorPositionBehavior=0
    ReportCodePageConversionErrors=0
    SelectMethod=0
    TruncateTimeTypeFractions=0
    WorkStationID=

    [mssqlserver]
    Driver=/usr/opt/ibm/WSII/odbc/ddl/lib/FRmsss21.so
    Description=MS SQL Server Driver for AIX
    Database=TraceTest
    LogonID=TraceAdmin
    Password=sqltest@123$
    Address=10.10.1.92\MSSQL2008
    QuotedId=YES
    QEWSD=41143
    AnsiNPW=YES

    [ODBC]
    IANAAppCodePage=4
    InstallDir=/usr/opt/ibm/WSII/odbc/ddl
    Trace=0
    TraceDll=/usr/opt/ibm/WSII/odbc/dd/lib/odbctrac.so
    TraceFile=odbctrace.out
    UseCursorLib=0
    $
    ----------------------------

    Please advise.

    Best Regards,

    Edited by: user13707876 on 23-Aug-2012 04:54
  • 2. Re: ORA-28500: connection from ORACLE to a non-Oracle system returned this
    Richard Harrison . Expert
    Currently Being Moderated
    Hi,
    Try removing the line

    set <envvar>=<value>


    from you hs init file - that may be confusing oracle?

    Cheers,
    Harry

    http://dbaharrison.blogspot.com
  • 3. Re: ORA-28500: connection from ORACLE to a non-Oracle system returned this
    user13707876 Newbie
    Currently Being Moderated
    Hi,

    After omitting the suggested line i.e. set <envvar>=<value> now i have encountered below error message please advise.
    --------------------
    SQL> select * from SQLTest@jadoo;
    select * from SQLTest@jadoo
    *
    ERROR at line 1:
    ORA-28500: connection from ORACLE to a non-Oracle system returned this message:
    ORA-02063: preceding line from JADOO


    SQL>
    ----------------------------AFTER THAT I HAVE DONE BELOW CHANGES FOUND as per suggestion of one of my firend in initdg4odbc.ora as :

    $ cat initdg4odbc.ora
    # This is a sample agent init file that contains the HS parameters that are
    # needed for the Database Gateway for ODBC

    #
    # HS init parameters
    #
    #HS_FDS_CONNECT_INFO = dg4odbc
    HS_FDS_CONNECT_INFO=mssqlserver
    HS_FDS_TRACE_LEVEL = off
    #HS_FDS_SHAREABLE_NAME = /usr/opt/ibm/WSII/odbc/ddl/lib/libodbc.a
    HS_FDS_SHAREABLE_NAME = /home/orauat/iiv/lib/odbc.so

    #
    # ODBC specific environment variables
    #
    set ODBCINI=/usr/opt/ibm/WSII/odbc/ddl/odbc.ini
    set ODBCINST=/usr/opt/ibm/WSII/odbc/ddl/odbcinst.so
    #
    # Environment variables required for the non-Oracle system
    #
    #set <envvar>=<value>
    $
    ----------------------------------------------------------------------------------------------------------------- NOW i have encounterd below error message.

    SQL> select * from SQLTest@ODBC;
    select * from SQLTest@ODBC
    *
    ERROR at line 1:
    ORA-02019: connection description for remote database not found


    SQL>


    Kindly advise solution.


    Best Regards,

    Edited by: user13707876 on 23-Aug-2012 21:53

    Edited by: user13707876 on 23-Aug-2012 22:12
  • 4. Re: ORA-28500: connection from ORACLE to a non-Oracle system returned this
    Richard Harrison . Expert
    Currently Being Moderated
    Hi,
    Please post your db link definition as well as tnsnames.ora and listener.ora.

    Cheers,
    Harry

Legend

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