Error 266 after executing SP

• mipo

  Ten Centuries

  Points: 1286

  More actions

  April 24, 2003 at 3:16 pm

  #81602

  I am facing this error when using a SP called by a DTS package (using ADO connection).

  The SP gets three parameters and looks like the following (just major part of it):

  SET @STR = 'SET ROWCOUNT 10000' + ' WHILE 1 = 1 ' + 'BEGIN' + ' BEGIN TRAN'

  SET @STR = @STR + ' DELETE FROM ' + @TableToBeCleaned + ' WHERE ' + @SELECTEDCOLUMN + ' < DATEADD (m,' + ' -' + @KeepMonthsInDatabase + ', + GETDATE())'

  SET @STR = @STR + 'IF @@ROWCOUNT = 0 ' + 'BREAK ' + 'COMMIT ' + 'END ' + 'SET ROWCOUNT 0'

  EXEC (@str)

  Following error appears:

  Server: Msg 266, Level 16, State 2, Line 1

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

  Server: Msg 266, Level 16, State 2, Procedure p_DBCleanerDeleteRows, Line 56

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

  I use the SET ROWCOUNT to execute the delete in batches. When checking with SELECT @@TRANCOUNT it returns me 5. Somehow it is clear to me that @@TRANCOUNT exited the SP with another value than when entering it but there is commit in the dynamic SQL statement and I do not know what is wrong.

  Is it neccessary to indicate BEGIN TRAN and COMMIT in the statement at all or can I ommit it?

  Thanks

  mipo

• Rajesh Patavardhan

  SSCrazy

  Points: 2801

  More actions

  April 25, 2003 at 12:53 am

  #455331

  Note that COMMIT statement will not execute if there is no data in @TableToBeCleaned satisfying the where clause.

  Change the fourth line as

  SET @STR = @STR + 'COMMIT ' + 'IF @@ROWCOUNT = 0 ' + 'BREAK ' + 'END ' + 'SET ROWCOUNT 0'