How to retrieve error code and message when distributed tx fails? (MS DTC)

Question

We have got a stored procedure that starts a distributed transaction via a linked server with different MS SQL 2008 databases.

We use

  SET XACT_ABORT ON;

and also

  BEGIN TRY / CATCH blocks

around the transaction to catch any errors and return the error code & message back to the calling client.

However, when a command inside the distributed transaction fails, it seems that the MS DTC is taking over control and our catch block can't rollback "gracefully" and return the error message etc. Instead an error is raised: The Microsoft Distributed Transaction Coordinator (MS DTC) has cancelled the distributed transaction. (Error 1206).

Is there any way that such a distributed tx error is caught by a catch block?

Answer 1

---UPDATE---

Looks like this is a known issue and Microsoft are not going to fix it: http://connect.microsoft.com/SQLServer/feedback/details/414969/uncatchable-error-when-distributed-transaction-is-aborted

There is a workaround but uses SSIS to call the SP: http://social.msdn.microsoft.com/Forums/en/sqlnetfx/thread/02e43cad-ac11-45fa-9281-6b4959929be7

---

You should be able to use XACT_STATE() to rollback the transaction and a RAISERROR coupled with @@ERROR to give you more details

  SET NOCOUNT ON
  SET XACT_ABORT ON
          BEGIN TRY
      BEGIN TRANSACTION

..code goes here

  COMMIT TRANSACTION; 

END TRY
BEGIN CATCH

  DECLARE @errormsg VARCHAR(MAX)
  SET @errormsg = @@ERROR

  -- Test XACT_STATE:
      -- If 1, the transaction is committable.
      -- If -1, the transaction is uncommittable and should 
      --     be rolled back.
      -- XACT_STATE = 0 means that there is no transaction and
      --     a commit or rollback operation would generate an error.

  -- Test whether the transaction is uncommittable.
  IF (XACT_STATE()) = -1
  BEGIN
      ROLLBACK TRANSACTION;
  END;

  -- Test whether the transaction is committable.
  IF (XACT_STATE()) = 1
  BEGIN
      COMMIT TRANSACTION;   
  END;

  RAISERROR(@errormsg, 16, 1)

END CATCH