Check Contraint Bypassing CATCH block in Distribut

I have a MSSSQL stored procedure performing a distributed transaction that looks like this:

SET XACT_ABORT ON;
SET NOCOUNT ON;

BEGIN TRY
  BEGIN DISTRIBUTED TRANSACTION


  insert into LNKSRV.INST.dbo.zz (id, val) values (1, 'a');
  insert into LNKSRV.INST.dbo.zz (id, val) values (2, 'b');


  COMMIT TRANSACTION
END TRY
BEGIN CATCH
  if (XACT_STATE() <> 0) 
  BEGIN
    ROLLBACK TRANSACTION;
  END
  print ERROR_MESSAGE();
  print ERROR_LINE();
  print ERROR_SEVERITY();

END CATCH

This works fine.

If I add this 3rd insert statement:

  insert into LNKSRV.INST.dbo.zz (id, val) values ('error', 'b');

...it fails correctly -- the transaction is rolled back on the remote server and control passes to the CATCH block and I get information about the error (can't convert 'error' to int).

But if I add this insert statement:

  insert into LNKSRV.INST.dbo.zz (id, val) values (-1, 'b');

..and I have a check contraint on the remote table requiring values > 0 in the id column, then things do not work as I expect. The transaction DOES roll back, but control DOES NOT transfer to the catch block. Instead, execution just dies and this is printed to the output window:

The Microsoft Distributed Transaction Coordinator (MS DTC) has cancelled the distributed transaction

Why? I need to log these errors in the catch blog.

Since the distributed transaction coordinator is handling this, when the transaction fails on the distributed part of the transaction, the DTC sends a message in the form of an attention, which stops your code from executing, and which the TRY/CATCH cannot process.

SQL Server can detect on your end when you are trying to insert an incorrect data type into a table (even on a remote instance) but the constraint is processed on the linked server, which causes the attention to be sent to DTC and your TRY/CATCH to be ignored.

For more information see the first "Note" section in the "Using TRY...CATCH in Transact-SQL" section of SQL Server 2008 Books Online, located at:

http://msdn.microsoft.com/en-us/library/ms179296.aspx