OraFAQ Forum: Networking and Gateways » can't insert data to SQL server within an oracle trigger

Home » RDBMS Server » Networking and Gateways » can't insert data to SQL server within an oracle trigger

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

can't insert data to SQL server within an oracle trigger [message #245002]

Thu, 14 June 2007 19:53

HC@ZZF
Messages: 2
Registered: June 2007

Junior Member

1) my simple trigger in oracle side
create or replace
TRIGGER TESTRI
AFTER INSERT ON TEST1
FOR EACH ROW
BEGIN
insert into test1@sqlserver (city,state) values ('what','nine');

END;

2)invoke trigger in oracel side.

SQL> insert into test1 values ('damn','da');
insert into test1 values ('damn','da')
*
ERROR at line 1:
ORA-02047: cannot join the distributed transaction in progress
ORA-06512: at "GGWEB.TESTRI", line 3
ORA-04088: error during execution of trigger 'GGWEB.TESTRI'

3) if insert date without inside a trigger, it works well

SQL> insert into test1@sqlserver (city,state) values ('what','nine');

1 row created.

SQL> commit;

Commit complete.

SQL> select * from test1@sqlserver;

CITY STATE
------------------------------ ----------
Fremont CA
Hayward CA
San Jose PA
what nine

SQL>

Report message to a moderator

Re: can't insert data to SQL server within an oracle trigger [message #245003 is a reply to message #245002]

Thu, 14 June 2007 21:17

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

02047, 00000, "cannot join the distributed transaction in progress"
// *Cause: Either a transaction is in progress against a remote database
//         that does not fully support two phase commit, and an update
//         is attempted on another database, or updates are pending and
//         and an attempt is made to update a different database that
//         does not fully support two phase commit.
// *Action: complete the current transaction and then resubmit the
//          update request.

Report message to a moderator

Re: can't insert data to SQL server within an oracle trigger [message #245230 is a reply to message #245002]

Fri, 15 June 2007 10:47

HC@ZZF
Messages: 2
Registered: June 2007

Junior Member

I know the reason, but how to fix it? Thanks!-Henry

Report message to a moderator

Re: can't insert data to SQL server within an oracle trigger [message #245240 is a reply to message #245002]

Fri, 15 June 2007 11:17

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

What happens if between the following 2 actions
>2)invoke trigger in oracel side.
>SQL> insert into test1 values ('damn','da');
you do the following instead?
2)invoke trigger in oracel side.
COMMIT;
SQL> insert into test1 values ('damn','da');

Report message to a moderator

Previous Topic:	ORA-12154: TNS:could not resolve service name.
Next Topic:	Accessing Oracle from Excel Outside the Firewall

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Apr 18 23:17:39 CDT 2024