Rollback Tran in Error Handler sp

• Donal Carpenter-402365

  Grasshopper

  Points: 14

  More actions

  August 27, 2008 at 3:39 am

  #191668

  Hi,

  I am trying to implement a system wide error handle sproc that will be called in the BEGIN/END catch of every stored procedure.

  To keep things clean and concise I check @@TRANCOUNT > 0 in the error handling sp, and if so I rollback.This works fine, the problem is though that the tran count at the start of the sproc was 1, and after it was 0 so SQL Server throws the following error;

  Msg 266, Level 16, State 2, Procedure CMS_HandleError, Line 0

  Transaction count after EXECUTE indicates that a COMMIT or ROLLBACK TRANSACTION statement is missing. Previous count = 1, current count = 0.

  Is there anyway to stop SQL Server from throwing this exception when the current count is less than the previous count?

  Thanks,

  Donal

• subrammail

  Old Hand

  Points: 332

  More actions

  February 17, 2010 at 12:37 am

  #1119881

  I am facing kind of problem, Any one help us:-)

• Paul White

  SSC Guru

  Points: 150467

  More actions

  February 17, 2010 at 4:32 am

  #1119952

  subrammail (2/17/2010)

  ---

  I am facing kind of problem, Any one help us:-)

  The original question was from August 2008.

  Tell us about your problem - it's likely to be different.

  Paul White
  SQLPerformance.com
  SQLkiwi blog
  @SQL_Kiwi