1

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

4
  • Are both servers behind the same firewall?
    – DeadZone
    Feb 3 '15 at 18:56
  • hello. Well every SO has your firewall. but i've disabled it and still didn't work. So I presume it's not this the problem. Thank you very much DeadZone.
    – Racer SQL
    Feb 3 '15 at 18:58
  • I know that you said that you disabled the firewall on server7, but this MS Support article has some nice troubleshooting steps to look at, and not all of them are firewall specific. A couple other ideas... Could Windows firewall have enabled when you disabled your firewall? Also, I saw something (but can't find the link) about cloning a server. Sometimes you need to uninstall MSDTC and reinstall it to get a new, unique ID. You might want to try that.
    – DeadZone
    Feb 3 '15 at 20:01
  • Yes DeaDZone, for sure I will try. Thank you very much. I'm testing some triggers here. the results are: server4 > server5 = OK server5 > server7 = NO! server7 > server5 = NO! I will take a look at your link.
    – Racer SQL
    Feb 4 '15 at 10:42
2

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.

0
1

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

  1. 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"

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

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

  1. 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;

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

EXEC master.sys.sp_MSset_oledb_prop;

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.