4 Replies Latest reply: Aug 27, 2012 2:41 PM by Richard Harrison . RSS

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

    user13707876
      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
          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 .
            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
              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 .
                Hi,
                Please post your db link definition as well as tnsnames.ora and listener.ora.

                Cheers,
                Harry