cross server updating via trigger issue

[david]

I need to update serverB.databaseB.tableB.columnB value
based on serverA.databaseA.tableA.columnA value change,

the update trigger in serverA.tableA works fine in
updating a testing databaseB.tableB.columnB in the same serverA.

serverB is as the linked server in ServerA and DTC is on.
when change the trigger to point to serverB.databaseB....
error:--------------------------------------------
Server: Msg 7395, Level 16, State 2, Procedure trInsUpdDel_InboundCannedMessages, Line 169
Unable to start a nested transaction for OLE DB provider 'SQLOLEDB'.
A nested transaction was required because the XACT_ABORT option was set to OFF.
[OLE/DB provider returned message: Cannot start more transactions on this session.]

thanks for the help
David

[MAk]

use this in those triggers before all the statements.

set xact_abort on


------------
david at 5/22/2002 8:05:07 PM

I need to update serverB.databaseB.tableB.columnB value
based on serverA.databaseA.tableA.columnA value change,

the update trigger in serverA.tableA works fine in
updating a testing databaseB.tableB.columnB in the same serverA.

serverB is as the linked server in ServerA and DTC is on.
when change the trigger to point to serverB.databaseB....
error:--------------------------------------------
Server: Msg 7395, Level 16, State 2, Procedure trInsUpdDel_InboundCannedMessages, Line 169
Unable to start a nested transaction for OLE DB provider 'SQLOLEDB'.
A nested transaction was required because the XACT_ABORT option was set to OFF.
[OLE/DB provider returned message: Cannot start more transactions on this session.]

thanks for the help
David

[David]

It works beautifully, after put set xact_abort on at the begin on
the trigger.
thank you very much.
David
------------
MAk at 5/23/2002 1:40:43 AM


use this in those triggers before all the statements.

set xact_abort on


------------
david at 5/22/2002 8:05:07 PM

I need to update serverB.databaseB.tableB.columnB value
based on serverA.databaseA.tableA.columnA value change,

the update trigger in serverA.tableA works fine in
updating a testing databaseB.tableB.columnB in the same serverA.

serverB is as the linked server in ServerA and DTC is on.
when change the trigger to point to serverB.databaseB....
error:--------------------------------------------
Server: Msg 7395, Level 16, State 2, Procedure trInsUpdDel_InboundCannedMessages, Line 169
Unable to start a nested transaction for OLE DB provider 'SQLOLEDB'.
A nested transaction was required because the XACT_ABORT option was set to OFF.
[OLE/DB provider returned message: Cannot start more transactions on this session.]

thanks for the help
David