Sql-server – trigger – Sql2008 – Msg 7391 – “no transaction is active/unable to begin trans”

msdtcoledbsql servertrigger

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.

update1:

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.

Update 2:

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 ).

Thanks.

Best Answer

FIXED!

Every site has the same step-by-step.

  • Start
  • DCOMCNFG
  • Expand "Component Services"
  • Expand "computers" >
  • Expand "My computer"
  • Expand "DTC"
  • right click on "Local DTC"
  • Properties
  • Security check "Network DTC Access" and " allow remote clientes".

It for sure will help a lot of people. The problem is that the server is in a cluster, so what a did is:

  • Start
  • DCOMCNFG
  • Expand "Component Services"
  • Expand "computers" >
  • Expand "My computer"

  • Expand "DTC"

  • Expand "CLUSTERED DTCs" << This is the trick

  • Right click on the server and make the same config as above for the "local DTC"

Restart service. Enjoy you boss Happy.

Related Question