rollback in script

• sql_prodigy

  SSCrazy

  Points: 2254

  More actions

  August 31, 2009 at 3:17 am

  #135287

  Hi everyone, im busy writing a script but my try catch and subsequent rollback function is not working can anyone shed some light?

  ive coded my rollback function within my catch function but even though i have errors in some of my insert and delete statements it never executes the catch block.

  what am i doing wrong here?

  BEGIN TRY

  BEGIN TRANSACTION

  //ALL my INSERT, UPDATE, DELETE statements IN here

  COMMIT;

  END TRY

  BEGIN CATCH

  IF @@TRANCOUNT > 0

  ROLLBACK

  PRINT 'Rollback executed, script contains errors.'

  DECLARE @ErrMsg nvarchar(4000), @ErrSeverity int

  SELECT @ErrMsg = ERROR_MESSAGE(),

  @ErrSeverity = ERROR_SEVERITY()

  RAISERROR(@ErrMsg, @ErrSeverity, 1)

  END CATCH

• slimchance99

  SSCrazy

  Points: 2661

  More actions

  September 1, 2009 at 8:57 am

  #1047594

  I think you have to put your TRY block inside of your transaction. Just switch the order of the BEGIN TRY and BEGIN TRAN statements and try it.

  Tim