1 Reply Latest reply: Feb 6, 2014 9:35 AM by Kgronau-Oracle RSS

    How to configure distributed transaction in Oracle Database Gateway for ODBC? (ORA-02047)

    4ec4ab6e-464c-4683-b3ad-554d7f6f0b78

      I am connecting from Oracle to another database server (MS SQL Server, DB2 the error message is the same) through an ODBC connection using Oracle SQL Developer. (This is how I set up)

       

      I want to query the schema of a non-Oracle database using the data dictionary mapping of the Oracle Database Gateway for ODBC.

       

      This works well:

      select * from all_tables@katimssql;

       

      But this doesn't:

      create table alltables_mssql as
      select * from all_tables@katimssql;

       

      Output:
      Error report:
      SQL Error: ORA-00604: error occurred at recursive SQL level 1
      ORA-02047: cannot join the distributed transaction in progress
      ORA-06512: at "SYS.HS$_DDTF_SQLTABLES", line 58
      ORA-06512: at line 1

      Does anyone know the solution of this?

      Thanks in advance,

      Kata


      Details:

      Oracle Database 11g Express Edition Release 11.2.0.2.0

      Microsoft SQL Server 2008 (SP2) - 10.0.4000.0 (X64)

      ODBC Driver: SQL Server Native Client 11.0 (32 bit)

       

       

      initkatimssql.ora:

      HS_FDS_CONNECT_INFO=katimssql
      HS_FDS_TRACE_LEVEL = ON
      HS_TRANSACTION_MODEL = READ_ONLY_AUTOCOMMIT

       

      I tried these, none of them worked, the error message is the same.

      HS_FDS_TRACE_LEVEL = off

      HS_TRANSACTION_MODEL = SINGLE_SITE

      HS_TRANSACTION_MODEL = READ_ONLY_AUTOCOMMIT

      HS_TRANSACTION_MODEL = READ_ONLY