Error 266 after executing SP

[mipo]

I am facing this error when using a SP called by a DTS package (using ADO connection).
The SP gets three parameter 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

[skhanal]

That is because for the last loop, you have begin tran and you break out of the loop, there is no rollback or commit for that.

Adding rollback tran before break will fix the problem, but don't forget to include rollback and break within begin/end after the if statement.