CODEWITHSUNDEEP
sql-server
praveen
praveen

Reputation: 12271

Log custom error message using try and catch block in sql server

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

Upvotes: 1

Views: 2846

Answers (2)

Khonsort
Khonsort

Reputation: 483

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.

Upvotes: 1

Martin Smith
Martin Smith

Reputation: 452978

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.

Upvotes: 2

Related Questions