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.

Naarah

Is this query/procedure is called from within another Query/Procedure/Code that has any other transaction? – Pyriphlegethon

@PranavSingh: the error is reproduced even in this simple context. In PROD, I have a more complex situation, with stored procedures calling others stored procedures calling others stored procedures, each SP having each transaction with SET XACT_ABORT ON. – Naarah

For simple context error is different i.e. FOREIGN KEY constraint FK_ error may be due to linkedsrv1.db1.[dbo].tbl1's column [IdWebsite] OR [IdProductType] has foreign key and value 1200 OR 0 for respective column might not be in their master... But for complex error might be due to some internal sp call that failed.. Best practice is not to depend on XACT_STATE() but check @@TRANCOUNT when palying with multiple level transactions. – Pyriphlegethon

Answer 1

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.

Belorussia

My suggestion would be to include a link to the MSDN docs that you are referencing. – Crescentic

+1, Thanks for the info, it seems all the conditions are met, except this "you try to release a rowset before all rows in the rowset are processed." which I don't get the meaning of. Also, I don't get the idea in the workaround, what should I do more exactly? – Naarah

@Naarah ... See the note just below the line.. Its related to ReleaseRows method in DT. – Belorussia

@bluefeet this is the MSDN Document support.microsoft.com/en-us/help/937517/… – Lacrosse

Answer 2

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

Sadesadella

Interesting idea... However, it seems the code is not executed in BEGIN CATCH area - for example if I add a PRINT as first line in BEGIN CATCH, I don't see any output... – Naarah

I see multiple ROLLBACK TRANSACTION statements, where is COMMIT TRANSACTION?. I smell something weird !!! – Sadesadella

This link may help you social.msdn.microsoft.com/Forums/sqlserver/en-US/… Have you enabled report proc execution ? exec sp_configure 'remote proc trans','1' reconfigure with override ? – Sadesadella

Looks like I got the ans! support.microsoft.com/kb/937517. If you turn off XACT_ABORT it may work for you. Give it try and please let me know If it works for you. – Sadesadella

Not succeeded after modifying remote proc trans. I cannot put XACT_ABORT OFF as this code is called in far more complex structure and I want to be sure the rollback is done correctly. – Naarah

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

Limbate

What's the logic of the transaction here? – Naarah

Added missing RAISERROR to rollback the transaction – Limbate