Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

BEGIN TRY/CATCH and MSDTC error

Tags:

People also ask

What is MSDTC?

Microsoft Distributed Transaction Coordinator (popularly known as MSDTC) is a coordinator to handle the distributed transactions. A distribution transaction involves two or more databases in a single transaction.

How do I disable MSDTC?

At the command prompt, type net stop msdtc , and then press ENTER.

What is MSDTC in Windows 10?

The Microsoft Distributed Transaction Coordinator (MSDTC) service is a component of modern versions of Microsoft Windows that is responsible for coordinating transactions that span multiple resource managers, such as databases, message queues, and file systems.


1/ The following code snippet show me the expected error: The INSERT statement conflicted with the FOREIGN KEY constraint FK_...

SET XACT_ABORT ON;

BEGIN TRANSACTION

    INSERT INTO linkedsrv1.db1.[dbo].tbl1 ([Col1], [Col2])  
    VALUES (1200, 0)                

COMMIT TRANSACTION

2/ But when I put this in a BEGIN TRY/CATCH, the error message is vague: Msg 1206, Level 18, State 118, Line 18 The Microsoft Distributed Transaction Coordinator (MS DTC) has cancelled the distributed transaction.

SET XACT_ABORT ON;

BEGIN TRY  
    BEGIN TRANSACTION   

        -- Error is on this line
        INSERT INTO linkedsrv1.db1.[dbo].tbl1 ([IdWebsite], [IdProductType])  
        VALUES (1200, 0)   

    COMMIT TRANSACTION
END TRY  
BEGIN CATCH
    PRINT 'Error' -- Code not reached

    SELECT ERROR_NUMBER(), ERROR_MESSAGE(), ERROR_SEVERITY(), ERROR_STATE()

    IF XACT_STATE() != 0   
        ROLLBACK TRANSACTION
END CATCH

Any idea why this happens?

Later edit:

  1. It works in case I remove the unneeded explicit transaction. It is still not clear why I get this error when I put BEGIN/COMMIT TRAN.

  2. I get the same error in case I have multiple inserts in multiple tables situated on linked server.

Any comment / remark is welcomed.