1

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.

  • 1
    Check your EventViewer for any errors logged by MSDTC (Microsoft Distributed Transaction Coordinator.) This error could have been caused by a lot of things. Hopefully you will get better information from the EventView and can edit your question with the additional information. – DeadZone Feb 3 '15 at 12:16
  • Hello. Checked, but didn't found anything. i can't even find something usefull on the internet. i really need to fix this. have created lots of linked servers but none of the fixed the problem. – Racer SQL Feb 3 '15 at 17:40
  • 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
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.

  • I am facing the same problem please help... – Parassharma1990 Mar 8 '17 at 8:35
  • Did you follow the step-by-step ? – Racer SQL Mar 9 '17 at 12:36
  • Hi Rafael Piccinelli . I followed all steps But at last I found that an antivirus which controls the system firewall is the main culprit. – Parassharma1990 Mar 11 '17 at 6:33

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.