XACT_ABORT

• egarre

  SSC Rookie

  Points: 48

  More actions

  March 28, 2002 at 8:54 am

  #78425

  Hi.

  I've read somewhere that if you had the XACT_ABORT option set to OFF (which is the default value) and an individual instruction fails in the middle of a transaction, SQL Server doesn't rollbacks the complete transaction.

  Well, if I desire this kind of behaviour, I must set the XACT_ABORT to ON. I've done that; in this case, when a transaction fails, SQL Server automatically rollbacks the complete transaction but it doesn't send any error message and I can't access to the

  @@error variable.

  Is there any way to be informed that a transaction has been rollbacked?

  I would appreciate any advice you could give me.

  Thank you very much in advance. Best regards from Spain.

  Enric Garre

  enric.garre@sema.es

• Steve Jones - SSC Editor

  SSC Guru

  Points: 738773

  More actions

  March 28, 2002 at 10:22 am

  #426236

  Can you post some code. Is @@error not set for the caller of the transaction?

  Steve Jones

  steve@dkranch.net

• egarre

  SSC Rookie

  Points: 48

  More actions

  April 2, 2002 at 12:46 am

  #426417

  CREATE PROCEDURE TryMe AS

  SET XACT_ABORT ON

  begin transaction

  insert into DPTOS (Code, Name) values ('AB', 'NameAB')

  insert into dptos (Code, Name) values ('DE', 'NameDE')

  update dptos set Code = 'AB' where code = 'DE'

  if @@error <> 0

  print 'Error'

  commit transaction

  If I set the stored procedure that way (with the XACT_ABORT ON), SQL Server doesn't print the error message.