BEGIN TRY/CATCH and MSDTC error

Question

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.

Answer 1

I have went to through this pain! If you are performing any CRUD operation on a single table TRANSACTION is not needed.

In this case, the problem is, XACT_STATE() returns -1 because there is an error in the active transaction. But, ROLLBACK TRANSACTION fails, since there is NO transactions happened. You did only one transaction, INSERT, which failed, so there are no other transactions to rollback.

Its always better to relay on @@TRANCOUNT than XACT_STATE() (at least in this case).

to make it work, change like this(though I don't support TRAN for single table):

IF @@TRANCOUNT > 0
ROLLBACK TRANSACTION

Answer 2

From MSDN:

SYMPTOMS

Consider the following scenario. You use the SQL Native Client OLE DB provider (SQLNCLI) in SQL Server 2005 to create a linked server. You create a distributed transaction. The distributed transaction contains a query that uses the linked server to retrieve data from a table. When you commit the distributed transaction, you may receive the following error message:

Msg 1206, Level 18, State 167, Line 3 
The Microsoft Distributed Transaction Coordinator (MS DTC) has cancelled 
the distributed transaction.

Additionally, you may receive the following error message when you run a query after this behavior occurs:

Msg 8525, Level 16, State 1, Line 1 
Distributed transaction completed. Either enlist this session in a new 
transaction or the NULL transaction.

This problem occurs if the following conditions are true:

You use the SQLNCLI provider to create a linked server between two 
instances of SQL Server 2005.

The XACT_ABORT option is set to ON.

In the distributed transaction, you try to release a rowset before 
all rows in the rowset are processed.

Note This problem may also occur if you call the ReleaseRows method in a distributed transaction to release a rowset before you commit a distributed transaction in an application.

CAUSE

This problem occurs because the SQLNCLI provider incorrectly sends an attention signal to the linked server to roll back the distributed transaction.

WORKAROUND

To prevent the SQLNCLI provider from sending an attention signal to the server, use the SQLNCLI provider to consume fully any rowsets that the OLE DB consumer creates.

Update

you need to configure 'remote proc trans' to "1" in server parameters.

Ex:

exec sp_configure 'remote proc trans','1' reconfigure with override

This will permmit you to execute any distributed queries.

More Update

If you are using .Net framework in front end too, then I think you can use TransactionScope Class. Remove transaction from query and put the Transaction in code level.

Answer 3

BEGIN TRANSACTION starts a distributed transaction between the server running the statements and the linked server, since potentially you can run updates against both servers. When the INSERT fails it needs to cancel the distributed transaction, thus the error you are getting. So you have to handle errors on two levels (insert and transaction). In this scenario, you'll need two TRY/CATCH blocks as follows:

SET XACT_ABORT ON;

BEGIN TRY  
    BEGIN TRANSACTION   

    BEGIN TRY
        -- Error is on this line
        INSERT INTO linkedsrv1.db1.[dbo].tbl1 ([IdWebsite], [IdProductType]) 
        VALUES (1200, 0)
    END TRY
    BEGIN CATCH
        SELECT 'Insert Error', ERROR_NUMBER(), ERROR_MESSAGE(), ERROR_SEVERITY(), ERROR_STATE()
        RAISERROR (15600,-1,-1, 'INSERT ERROR');
    END CATCH 

    COMMIT TRANSACTION
END TRY  
BEGIN CATCH
    SELECT 'Transaction Error', ERROR_NUMBER(), ERROR_MESSAGE(), ERROR_SEVERITY(), ERROR_STATE()

    IF XACT_STATE() != 0   
        ROLLBACK TRANSACTION
END CATCH