I use a trigger which I update a linked server and this is working very good in SQL 2000. But after I migrate the database to SQL 2005. The trigger is not working with linked server any more. I remember I need to start the distributed transaction coordinator in service manager. But I couldn't find anything similar to this in SQL 2005. Is it somewhere I need to start? I got an error message like following:
Msg 7391, Level 16, State 2, Procedure EAI_ADM_STUDENT_ins_upd_trg, Line 804
The operation could not be performed because OLE DB provider "SQLNCLI" for
linked server "10.2.0.119" was unable to begin a distributed transaction.
Have you checked Linked Server Properties?
There are Server Options
If you switch:
RPC = true
RPC Out = true
on both servers, you should have not any problems.
TSQL equivalent of above is:
EXEC master.dbo.sp_serveroption @server=N'MyServerName', @optname=N'rpc', @optvalue=N'true'
EXEC master.dbo.sp_serveroption @server=N'MyServerName', @optname=N'rpc out', @optvalue=N'true'