Search code examples
sql-servertransactionsssmslinked-servermsdtc

linked server : "No transaction is active." and "unable to begin a distributed transaction"

I have a CRM software on my local machine. In the SQL DB of the software, I added a linked server (our test website with our test DB: Windows Small Business Server 2011). The linked server is set as

RPC => True
RPC Out => True
Enable Promotion of Distributed Transaction => True

I created a trigger so that when a company is updated through the CRM software, it launches a stored procedure such as:

proc [dbo].[Proc_UpdateInstitutionWeb]
@Company_ID nvarchar(50)

as

DECLARE @id int = 0;

BEGIN TRY
    SET @id = CONVERT(int, @Company_ID)
END TRY
BEGIN CATCH
    ...
END CATCH

update [myserver].[mydatabase].[dbo].[company] 
set founded = 2000
where company.id= @id

This does not work when I do the change in the CRM. So I tried a simple update query on the company table in the CRM SQL DB but it throws the following error:

OLE DB provider "SQLNCLI10" for linked server "myserver" returned message "No transaction is active.". Msg 7391, Level 16, State 2, Procedure Proc_UpdateInstitutionWeb, Line 34 The operation could not be performed because OLE DB provider "SQLNCLI10" for linked server "myserver" was unable to begin a distributed transaction.

Here is what I tried to do:

On both my computer and the test server:

  • In admin tools -> Services -> Distributed Transaction Coordinator : started the services
  • In admin tools -> Component Services -> Computers -> My Computer -> Distributed Transaction Coordinator -> Local DTC -> Right Click -> Security Tab -> checked network DTC Access and Allow remote Clients and allow inbound and outbound.
  • Finally I restarted the test server

None of this solved the problem yet. What should I do?

thank you

Solution

  • After many days losing my hair, here is what worked for me, in case someone needs it.

    First I disabled automatic 6to4 address generation.

     1. Open Command
 2. Type the command `netsh interface 6to4 set state disabled`

    Then I restricted the DCOM port range on both machine

     1. Start -> Run -> Type DCOMCNFG
 2. Go to the properties of the My Computer node under the Computers folder underneath Component Services. 
 3. Under the My Computer Properties look under the Default Protocols tab. 
 4. Over there make sure that Connection-oriented TCP/IP is selected and then click on Properties. 
 5. No range was listed. I added one. Example 5000-5100

    I think all the previous work on the DTC in the Component services and Services is also required to have this working.

    I ended up with that solution working with DTCPing.exe and the following references: