Update to Linked Server via trigger fails, but works without trigger. Unable to begin a distributed transaction

Question

I have a SQL Linked Server setup on server #1 to access server #2. When I run an update or insert statement in SSMS on server #1 that changes data in server #2 it works. Now I use the exact same UPDATE statement in a trigger on a table (on server #1), and then it fails with the error:

OLE DB provider "SQLNCLI11" for linked server "172.xx.xx.02" returned message "No transaction is active.". Msg 7391, Level 16, State 2, Procedure MyProc, Line 12 The operation could not be performed because OLE DB provider "SQLNCLI11" for linked server "172.xx.xx.02" was unable to begin a distributed transaction.

Since a normal UPDATE/INSERT is working, I'm going to assume the DTC is setup correctly and working (or is this a wrong assumption?). As almost all the help on the web refers to DTC, I'm pasting my DTC settings below anyway. Anything else that it can be? (and before anyone critiques Linked Servers, yes I know it's old and not the preferred way, but can't change it now - it's a legacy system).

My Trigger looks like this:

ALTER TRIGGER [dbo].[MyTrigger]
  ON [dbo].[Table1]
  FOR UPDATE, INSERT
  AS
BEGIN 

INSERT INTO [172.xx.xx.02].SomeDB.dbo.MyTable (Field1, Field2)
VALUES ('34172', GETDATE())
END 

Linked Server Properties:[]1

DTC:

EDIT: Although I'm still looking for answers and will assign the bounty to any correct answer, I did find this work-around as a temporary solution: I can change the trigger to run a SQL statement directly on the remote server, thereby bypassing the DTC.

CREATE TRIGGER [dbo].[MyTrigger]
  ON [dbo].[MyTable]
  FOR UPDATE, INSERT
  AS
BEGIN 

    -- Perform the update on the linked server without distributed transaction
    DECLARE @sql NVARCHAR(MAX);
    SET @sql = N'
        INSERT INTO [172.1.1.10].SomeDB.dbo.MyTable (Col1, Col2)
        VALUES (''ABC'', GETDATE()';

    -- Execute the update using sp_executesql
    EXEC [172.1.1.10].master.sys.sp_executesql
        @sql;
END;

Answer 1

"was unable to begin a distributed transaction":

If the "Enable Promotion of Distributed Transactions" is set to False in the linked server properties, it might be the cause of the issue. When set to false, it means that Microsoft SQL Server will not use Microsoft Distributed Transaction Coordinator (MS DTC) to coordinate transactions that are distributed across multiple instances of SQL Server.

Try changing "Enable Promotion of Distributed Transactions" to True.
You might need to restart the SQL service for the changes to take effect.

Besides that, try and follow the official Microsoft page: "Troubleshooting Problems with MSDTC".

---

If changing the "Enable Promotion of Distributed Transactions" to True did not resolve the issue, and the DTC appears to be set up correctly, there might be other reasons for the behavior.

Triggers, by their nature, execute within the scope of the action that fired them. If the outer operation (the INSERT or UPDATE that fires the trigger) is already within a transaction, and then the trigger tries to perform a distributed operation, this could be problematic.
Consider whether the original operation is wrapped in a transaction and if that might be the cause.

And make sure the account SQL Server is running under on both Server #1 and Server #2 has adequate permissions to initiate a distributed transaction.

If the trigger is affecting multiple databases (even on the same server), SQL Server might be promoting the transaction to a distributed transaction. If any of these databases are read-only or have some other constraints, it could cause the error.

---

Alternative Approaches, as a workaround (not necessarily a direct solution)

• You can use the Service Broker to send messages from one server to another, effectively decoupling the operations and avoiding the need for distributed transactions.
• Instead of using a trigger, you can accumulate changes in a staging table on Server #1 and have a scheduled SQL job (via SQL Agent) periodically push changes to Server #2.

---

Still no success. I assume something is wrong with the DTC, but exactly what I can't tell. I did find a work around which I've added to the question. Other suggestions are still welcome.

Your workaround leverages the sp_executesql stored procedure to execute the SQL statement on the remote server. That approach effectively decouples the operations, bypassing the Distributed Transaction Coordinator (DTC) and preventing it from promoting the operation to a distributed transaction.

But you could also:

• make sure any dynamic parameters in the SQL string are parameterized or sanitized appropriately.
• heck for affected rows: since this trigger acts upon INSERT or UPDATE, there might be scenarios where multiple rows are affected. Make sure your dynamic SQL handles all necessary rows.
• incorporate a TRY-CATCH block to capture and handle any errors that might arise during the execution of the remote SQL.

On the last point:

CREATE TRIGGER [dbo].[MyTrigger]
ON [dbo].[MyTable]
FOR UPDATE, INSERT
AS
BEGIN

    BEGIN TRY
        -- Perform the update on the linked server without distributed transaction
        DECLARE @sql NVARCHAR(MAX);
        SET @sql = N'
            INSERT INTO [172.1.1.10].SomeDB.dbo.MyTable (Col1, Col2)
            VALUES (''ABC'', GETDATE()';
        
        -- Execute the update using sp_executesql
        EXEC [172.1.1.10].master.sys.sp_executesql @sql;
        
    END TRY
    BEGIN CATCH
        -- Capture the error information and handle the error
        DECLARE @ErrorMessage NVARCHAR(4000), 
                @ErrorSeverity INT, 
                @ErrorState INT;
        
        SELECT @ErrorMessage = ERROR_MESSAGE(),
               @ErrorSeverity = ERROR_SEVERITY(),
               @ErrorState = ERROR_STATE();
        
        -- For this example, I'm raising the error to show how to capture it
        -- In a real scenario, you might log the error or take another appropriate action
        RAISERROR (@ErrorMessage, @ErrorSeverity, @ErrorState);
        
    END CATCH

END;

Inside the CATCH block, the error details are captured using the ERROR_MESSAGE(), ERROR_SEVERITY(), and ERROR_STATE() functions.
Then, for demonstration purposes, I have used the RAISERROR function to raise the error, but you can replace that with logging or other error-handling actions as appropriate for your environment.

---

Some feedback is the error being returned with the TRY-CATCH is:

OLE DB provider "SQLNCLI11" for linked server "172.1.1.0" returned message "No transaction is active.".
Msg 0, Level 11, State 0, Line 0 A severe error occurred on the current command. The results, if any, should be discarded.

As an alternative, you could try and a void direct remote execution in trigger:

• Instead of directly executing the SQL on the remote server within the trigger, consider logging the necessary data to a local table in Server #1 (a sort of "staging" table).
• Then, create a SQL Server Agent Job (or any other scheduling mechanism you have) that periodically reads from this staging table and pushes the data to Server #2, outside the context of a trigger. This ensures that the transactional context of the trigger doesn't interfere with the distributed transaction.

In SQL Server Management Studio, make sure "Implicit Transactions" is turned off. If turned on, every query you run is automatically wrapped in a transaction. You can toggle it off with:

SET IMPLICIT_TRANSACTIONS OFF;

And make sure that the option “Enable Promotion of Distributed Transaction” for the linked server is indeed set to False. This setting is supposed to prevent SQL Server from trying to start a distributed transaction.