I have a trigger on my SqlServer, located in server7.
It inserts a data on another server ( server5 ), after insert, as you see the code below:
CREATETRIGGER [dbo].[trgSPTInsereDepartamento] ON [dbo].[tblDepartamento] AFTER INSERT AS BEGIN SET NOCOUNT ON; insert into [Server5].alfabase.dbo.tblDepartamento (CodDepto,DescDepto,IncluiOutroDepto) Select ins.codDepartamento, ins.DescDepartamento, 0 From inserted ins where not exists(select 1 from [Server5].alfabase.dbo.tblDepartamento sptsta where sptsta.CodDepto = ins.CodDepartamento) END
When i make an insert, the SQL shows me this:
OLE DB provider "SQLNCLI10" for linked server "server5" returned message "No transaction is active.". Msg 7391, Level 16, State 2, Procedure trgSPTInsereDepartamento, Line 7 The operation could not be performed because OLE DB provider "SQLNCLI10" for linked server "server5" was unable to begin a distributed transaction.
Local DTC properties is all checked ( it allows remote users ).
I've restarded the server and the DTC service. No Luck for me.
I read a lot of posts but none of them helped me.
Thanks for the help.
Checked firewall. disabled it but still doesn't work.
DTC is ok ( only server5 is on a cluster. )
I'm really out of ideas guys.
Thank you very much.
From server7 to server5, I have the problem above. To test, I made a trigger on a database from server4 to server5. and it worked. So, the problem is on server7.
Server7 is not on the Cluster ( server3,4,5 are clustered ).