while(
@rowsLeft > 0)
begin
begin transaction ArchiveTran
begin
begin try
set
@iterationNumber =
@iterationNumber + 1
set
@iterationtime = CURRENT_TIMESTAMP
INSERT INTO
@asyncLogsForDeletePerIteration (AsyncOperationId)
Select top (
@rowsPerIteration) AsyncOperationId FROM
@totalAsyncLogsForDelete Delete aob FROM GPBL_MSCRM.dbo.AsyncOperationBase aob
inner join
@asyncLogsForDeletePerIteration as asyncForDel on asyncForDel.AsyncOperationId = aob.AsyncOperationId
Delete total from
@totalAsyncLogsForDelete total
inner join
@asyncLogsForDeletePerIteration iter on iter.AsyncOperationId = total.AsyncOperationId
set
@rowsLeft =
@rowsLeft - (select count(*) from
@asyncLogsForDeletePerIteration) ;
set
@duration = getdate()-
@iterationtime;
PRINT 'Iteration successfully compleated. Rows left' + isnull(CAST (
@rowsLeft as nvarchar) ,'') + 'Iteration time: ' + isnull(CAST(
@iterationtime as nvarchar),'')
end try
begin catch
if @
@TRANCOUNT>0 rollback;
set
@rowsLeft =
@rowsLeft +
@rowsPerIteration;
print concat('Transaction failed: ', error_message(), ' on iteration: ', isnull(cast(
@iterationNumber as nvarchar),'') , 'Error timestamp: ', ' Rows left :', isnull(cast(
@rowsLeft as nvarchar),'') )
end catch
commit tran ArchiveTran
end
set
@iterationtime = current_timestamp
end -- loop
