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.
At the command prompt, type net stop msdtc , and then press ENTER.
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:
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.
I get the same error in case I have multiple inserts in multiple tables situated on linked server.
Any comment / remark is welcomed.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With