August 8, 2008 at 7:27 am
Hello,
I have two instances of SQL Server 2000 on two physically separate servers. (serv1 and serv2)
I have created a linked server on serv2 for accessing serv1. I have similar tables on both the servers. One update trigger is written on serv2 so that any updates on the table of serv2 can be updated to Serv1.
Serv1 has Win. Server 2000 and
Serv2 has Win. Server 2003.
I have enabled Network DTC Access on serv2.
But when serv2 table is updated I get the following error.
Server: Msg 7391, Level 16, State 1, Procedure TRG_TRANSFER_INVESTUPDATES, Line 6
The operation could not be performed because the OLE DB provider 'MSDASQL' was unable to begin a distributed transaction.
[OLE/DB provider returned message: [Microsoft][ODBC SQL Server Driver]Distributed transaction error]
OLE DB error trace [OLE/DB Provider 'MSDASQL' ITransactionJoin::JoinTransaction returned 0x8004d00a].
If I disable the trigger and then update the table of the linked server then the record on linked server is updated without any error.
Does anyone know what is the reason for this error and how to resolve it?
Thanks in advance
Regards,
Rohit
August 10, 2008 at 10:12 pm
Can you post the trigger schema you created and the process of updating the link server?
"-=Still Learning=-"
Lester Policarpio
August 10, 2008 at 10:25 pm
Hi Thanks for the reply. Below is the trigger that I am using.
create Trigger TRG_TriggerName
ON
CurrentDB_Table
FOR UPDATE
AS
update
LinkedServerName.DBName.dbo.tablename
set
Columnname=d.columname,
from
LinkedServerName.DBName.dbo.tablename ap
inner join deleted d on d.colname = ap.colname
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply