1

I'm trying to insert a duplicate value in to a primary key column which raises a primary key violation error.I want to log this error inside the catch block .

Code Block :- 

   SET XACT_ABORT OFF
   BEGIN TRY
   BEGIN TRAN
   INSERT INTO #Calender values (9,'Oct')
   INSERT INTO #Calender values (2,'Unknown')
   COMMIT TRAN
   END TRY

  BEGIN CATCH 
  Insert into #LogError values (1,'Error while inserting a duplicate value')
  if @@TRANCOUNT >0
  rollback tran
  raiserror('Error while inserting a duplicate value ',16,20)
  END CATCH

when i execute the above code it prints out the custom error message which is displayed in the catch block but doesn't insert the value in to the #LogError table

Error while inserting a duplicate value

But when i use SET XACT_ABORT ON i get a different error message but still it doesn't inserts the error message into the table

 The current transaction cannot be committed and cannot support operations
 that write to the log file. Roll back the transaction.

My question is

1.How to log error into the table

2.Why do i get different error message when i set xact_ABORT on .Is it a good practice to set XACT_ABORT on before every transaction

praveen
  • 12,083
  • 1
  • 41
  • 49
  • 1
    Regarding good practice see [this answer here](http://stackoverflow.com/a/1150350/73226) and discussion in comments. – Martin Smith Jun 10 '12 at 10:35

2 Answers2

2

It does insert the record into #LogError but then you rollback the transaction which removes it.

You need to do the insert after the rollback or insert into a table variable instead (that are not affected by the rollback).

When an error is encountered in the try block this can leave your transaction in a doomed state. You should test the value of XACT_STATE() (see example c in the TRY ... CATCH topic) in the catch block to check for this before doing anything that writes to the log or trying to commit.

When XACT_ABORT is on any error of severity > 10 in a try block will have this effect.

Martin Smith
  • 438,706
  • 87
  • 741
  • 845
  • So basically i should write to the log file when XACT_STATE()=0 and when it is 1 or -1 i should rollback the trasnaction .Is it rit ? – praveen Jun 10 '12 at 10:42
  • 1
    @praveen - When it is 1 you can perform actions that write to the log if you want to. – Martin Smith Jun 10 '12 at 10:51
  • Martin :I think XACT_STATE() will never be 1 if i get an error in try block because the transaction will be in uncommitable state .So i think it should be placed inside the code block when xact_state()=-1 The code which i have written is if XACT_STATE()=-1 BEGIN rollback tran Insert into #LogError values (1,'Error while inserting a duplicate value') END – praveen Jun 10 '12 at 11:11
  • @praveen - It can be 1 in a try block if the code isn't been run with `XACT_ABORT ON` otherwise yes I don't think that is possible. – Martin Smith Jun 10 '12 at 11:14
1

As SqlServer doesn't support Autonomous transaction (nested and independent transaction), it's not possible (in fact, you can, under some condition, use CLR SP with custom connectstring - doing it's own, non local, connection) to use a database table to log SP execution activity/error messages.

To fix, this missing functionnality, I've developed a toolbox (100% T-SQL) based on the use of XML parameter passed as reference (OUTPUT parameter) which is filled during SP execution and can be save into a dedicated database table at the end.

Disclamer: I'm a Iorga employee (cofounder) and I've developped the following LGPL v3 toolbox. My objective is not Iorga/self promotion but sharing knowledge and simplify T-SQL developper life.

See, teach, enhance as you wish SPLogger

Today (October 19th of 2015) I've just released the 1.3 including a Unit Test System based on SPLogger.

Khonsort
  • 483
  • 4
  • 6