Hi

I need to update AS400 Linked server tables as soon as a table on SQL server 2005 is updated. I have created a trigger for the same which is given below:

Create Trigger testTrigger

on testTable

For UPDATE

AS

DECLARE @count int;

Begin

-- SET NOCOUNT ON added to prevent extra result sets from

-- interfering with SELECT statements.

SET NOCOUNT ON;

-- select the rows from inserted table

select @count=count(*) from testTable t inner join inserted i on t.id = i.id

if @count > 0

Update LinkedAS400.xyz.abc.as400testtable set var1 = ('X') Where var2=1

End

Go

The update query works fine when executed separately, but from a trigger, it returns with the following error

"Msg 7390, Level 16, State 2, Procedure testTrigger

The requested operation could not be performed because OLE DB provider "IBMDA400" for linked server "LinkedAS400" does not support the required transaction interface."

The MSDTC service is started and the journals have also been turned on on the AS400 side.

What else am I missing here?