trigger - SQL Server 2008 - Msg 7391 - "no transaction is active/unable to begin trans"

Question

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

Answer 1

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.

Answer 2

We're also facing this issue & just resolved :)

We tried to link from MsSQL2008R2 (source) to MsSQL2019 (target). Both DB servers (VM) are located in the same domain - the 1st and 2nd ocate of the physical IP address same.

Before this, able to create the "linked server" at Source & Target servers. We'd also used the same ID&password for authentication at both sides. But hit error when tried to execute.

Sharing, the things we'd done so far:

1. Ensure the MSDTC Windows service is started successfully

Service Name: Distributed Transaction Coordinator

2. Ensure network & firewall connection (server's firewall also disabled) between Source & Target servers are OK.

At DOS, type this command to check: netstat -an | find "port no"

3. Disable automatic 6to4 address generation.

Run these commands at DOS with admin rights:

netsh interface 6to4 set state disabled

netsh interface 6to4 show state

Do this at Source server only since it's SQL2008.

4. Update the C:\windows\system32\drivers\etc*hosts* and lmhosts files.

Did this at both Source & Target servers.

Not only I added the DB server's original hostname, but added the linked server label name (what you defined at SQL), in case unable to decipher properly.

For e.g.,

99.999.99.999 source

99.999.99.999 linkto_source

88.888.88.888 target

88.888.88.888 linkto_target

5. Run this at SQL to ensure the "Ad Hoc Distributed Queries" value is 1

USE master;

GO

EXEC sp_configure 'show advanced option', '1';

RECONFIGURE;

GO

EXEC sp_configure;

6. Run this at SQL to ensure the "SQLNCL10" value under column "allow_in_process" is 1

EXEC master.sys.sp_MSset_oledb_prop;

7. Check and configure these settings at both Source & Target servers

• From DOS, type DCOMCNFG

• Expand and select Console Root -> Component Services -> Computers -> My Computer -> Distributed Transaction Coordinator -> Local DTC

• Right click on Local DTC and select the Properties option

• Select the Security tab and do the below configurations:

• Network DTC Access -> checked

• Client and Administration - Allow Remote Clients -> checked

• Client and Administration - Allow Remote Administration -> checked

• Transaction Manager Communication - Allow inbound -> checked

• Transaction Manager Communication - Allow outbound -> checked

• Transaction Manager Communication - No authentication required -> checked

• Enable XA Transactions -> unchecked

• Enable SNA LU 6.2 Transactions -> checked

• DTC Logon account - Account -> NT AUTHORITY\Network Service

Hope the above helps :)