trigger - SQL Server 2008 - Msg 7391 - "no transaction is active/unable to begin trans"
1
vote
2
answers
10068
views
I have a trigger on my SQL Server, 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).
Asked by Racer SQL
(7546 rep)
Feb 3, 2015, 11:00 AM
Last activity: Aug 13, 2021, 08:58 AM
Last activity: Aug 13, 2021, 08:58 AM