@@TRANCOUNT incremented when SP is started, not when BEGIN TRAN is executed

• santhosh_kumar012000

  SSC Rookie

  Points: 33

  More actions

  February 2, 2012 at 3:30 am

  #99947

  Hello all,

  I would greatly appreciate if anyone could be of help here. I have a SP that has begi/commit/rollback transaction commands.

  When I am executing the SP like

  EXECUTE [Library].[dbo].[cat_cat_CatalogRequestsProcess_b]

  10016--@in_UserId

  ,@out_rc OUTPUT; select @out_rc;

  The @@TRANCOUNT is immediately incremented to 1 as soon as the control enters the SP. Then I have a BEGIN TRAN command inside the SP. But that command does not change the @@TRANCOUNT at all. The same is with ROLLBACK command.

  I expect the @@TRANCOUNT to increment to 1 only when it encounters BEGIN TRAN command not when SP is start executing.

  Since @@TRANCOUNT is not incremented I am not able to determine whether I am on a transaction or not and hence getting the message

  --Transaction count after EXECUTE indicates a mismatching number of BEGIN and COMMIT statements. Previous count = 0, current count = 1.--

  I am using SQL Server Express 2008 R2

  Please help

  Thanks

• fahey.jonathan

  Hall of Fame

  Points: 3617

  More actions

  February 7, 2012 at 9:56 am

  #1443929

  Having the text of the stored procedure would help us to determine the problem. Please post it if you have it available.