CREATE PROCEDURE [dbo].[UpdInsDeltrans]
AS
BEGIN
SET NOCOUNT ON

SET XACT_ABORT ON

BEGIN TRY
BEGIN DISTRIBUTED TRAN

UPDATE srvr2.db2.dbo.tbl_a
SET COL1 = col1 + 10

UPDATE srvr2.db2.dbo.tbl_a
SET COL2 = col2 + 6

INSERT INTO srvr2.db2.dbo.tbl_a (col1, col2)
VALUES (9999, 8885)

DELETE FROM srvr2.db2.dbo.tbl_a
WHERE col1 < 100

COMMIT TRAN
INSERT INTO ResultLog (error, time)
VALUES ('success', getdate())

END TRY
BEGIN CATCH

ROLLBACK TRAN

INSERT INTO ResultLog (error, time)
VALUES (ERROR_MESSAGE(), getdate())

END CATCH

END




In this stored procedure when I get the error I need to database that error.

Because of XACT_ABORT ON the transaction is successfully rolledback but not able to record error.

Does anyone have ideas or sample code?