6 Replies Latest reply: Jun 25, 2012 11:09 AM by 510477 RSS

    Getting error trying to post to SQL Server

    510477
      We're trying to update an external SQL Server with tracking information for a customer. Does anyone know how to track down and solve the cause of the following error:

      Fri Jun 08 10:19:42 2012
      Error 1010 trapped in 2PC on transaction 3.28.380440. Cleaning up.
      Error stack returned to user:
      ORA-01010: invalid OCI operation
      ORA-02063: preceding line from SQLWEB

      SQLWEB is the name of the external connection.

      The odd thing is that if the entire transaction relies on triggers, it works fine. It is only when we try to manually insert into the tracking table (which then fires the final insert trigger) that we get the error. And it only happens about 50% of the time.

      ???
        • 1. Re: Getting error trying to post to SQL Server
          Kgronau-Oracle
          I would first concentrate on the 2PC error - what gateway are you using (DG4ODBC or DG4MSQL)?
          • 2. Re: Getting error trying to post to SQL Server
            510477
            ODBC. What is really strange is that it is a trigger doing the actual ODBC communication in either case - the only difference is how the original record gets inserted into the table which in turn fires the trigger off.
            • 3. Re: Getting error trying to post to SQL Server
              Mkirtley-Oracle
              Hi,
              DG4ODBC can't take part in distributed transactions. There needs to be a commit (or rollback) between any insert to Oracle and an insert using the gateway.
              You can't do -

              insert into oracle_table ;
              insert into no_oracle-table ;

              it needs to be -

              insert into oracle_table ;
              commit ;
              insert into no_oracle-table ;

              So, it depends when and where your trigger fires and if there are any commits.

              Regards,
              Mike
              • 4. Re: Getting error trying to post to SQL Server
                510477
                The trigger has one purpose and one purpose only - to push the data up to the SQL Server on insert of a new record. Here it is:
                CREATE OR REPLACE TRIGGER ALTAFAB_SQLPUSH
                  AFTER INSERT OR UPDATE ON ALTAFAB_TRACKING
                  FOR EACH ROW
                DECLARE
                  PRAGMA AUTONOMOUS_TRANSACTION;
                  trk_id      VARCHAR2(50);
                  trk_spd     VARCHAR2(10);
                BEGIN
                  trk_id    := to_char(:new.POWER_UNIT);
                  trk_spd   := to_char(:new.SPEED||' '||:new.SPEED_UOM);
                  DELETE FROM mancamp_location@sqlweb
                   where "UnitID" = trk_id;
                  IF :new.UNIT > 0 THEN
                    INSERT INTO mancamp_location@sqlweb
                      ("UnitID", "ManCampID", "Lat", "Long", "UpdateDT", "VehSpeed", "VehDirection", "Landmark")
                      values (:new.POWER_UNIT, :new.UNIT, :new.GPS_LATITUDE, :new.GPS_LONGITUDE, :new.DATE_TIME
                           , trk_spd, :new.DIRECTION, :new.LANDMARK);
                  END IF;
                  COMMIT;
                END;
                /
                I'm not trying to "mix" anything and it is working fine for the most part. The only time I get the error (and it's only about 50% of the time) is when I try to insert into the original table using an APEX form - data that flows through from my 3rd party app doesn't even hiccup. So I know the connection is set up properly - there is something about the difference in insert methods into the table that is causing the problem.
                • 5. Re: Getting error trying to post to SQL Server
                  Mkirtley-Oracle
                  Hi,
                  What you may find is that the third party application is making an autocommit after the insert into Oracle but this is not happening after the APEX insert.
                  The autocommit makes the updates non-distributed but without a commit you then get a distributed transaction which will cause errors if the update fails.
                  So, if I understand it correctly it looks like 2 separate problems -
                  - the insert is failing sometimes.
                  - you get a 2PC error because DG4ODBC cannot take part in distributed transactions

                  You could setup gateway debug tracing to see what errors you get when inserting manually and you see the 2PC error, but you also need to look at the workflow to prevent the transactions being distributed.

                  Regards,
                  Mike
                  • 6. Re: Getting error trying to post to SQL Server
                    510477
                    So it turned out to be a simple thing: I needed to put an explicit commit into the APEX insert call. Now it works every time and no errors.

                    Lesson learned: the simplest solution is usually the correct one. Thanks to mkirtley for making me think about the commit.