Print ''
Print '----------------------------------------------------'
Print ' Cleanup ProcessData '
Print ' FirstRevision: V2.1.1   17.12.2012'
-- Print ' CurrRevision : V'
Print ' Autor        : J.Feuerstein'
Print ' '
Print ' Database Name: #DB_N#'
Print '----------------------------------------------------'
GO

use [#DB_N#]

Print ''
Print '#Step: Cleanup all NON Last ProcessData Results'

DECLARE @DeletedCounter INT
DECLARE @DeleteN INT
SET @DeleteN = 0
SET @DeletedCounter = 0

declare @Id  uniqueidentifier,
        @ObjectCliName nvarchar(255),
        @ObjectFromSiteID nvarchar(32),
        @ProcessPacketName nvarchar(255)

set @ObjectCliName=''

select @DeleteN=count(*)
FROM
  dbo.JobControl_ObjectsProcessData_tbl
WHERE
  dbo.JobControl_ObjectsProcessData_tbl.Row_isLastRes <> 1

print('.')
Print '-------------------------------------------------------------------------'
print('Delete in DB...............: ' + '#DB_N#')
print('Non Last Process Results N.: [' + CONVERT(nvarchar(64),@DeleteN) + ']')
Print '-------------------------------------------------------------------------'


-- DELETE
-- FROM
--   dbo.JobControl_ObjectsProcessData_tbl
-- WHERE
--   dbo.JobControl_ObjectsProcessData_tbl.Row_isLastRes <> 1


-- Curor fuer alle zu durchlaufenden Objekte erstellen
declare DeleteTblCursor cursor fast_forward for
select Id, ObjectCliName, ObjectFromSiteID, ProcessPacketName
FROM
  dbo.JobControl_ObjectsProcessData_tbl
WHERE
  dbo.JobControl_ObjectsProcessData_tbl.Row_isLastRes <> 1


set nocount on
open DeleteTblCursor
 -- alle Objekte durchlaufen die durch den Cursor durchlaufen
 while 1=1
  begin
    SET @DeletedCounter = @DeletedCounter + 1
    fetch next from DeleteTblCursor into @Id, @ObjectCliName, @ObjectFromSiteID, @ProcessPacketName

    -- Ausstieg wenn letztes Element
    if (@@fetch_status <> 0) break
    -- if (@DeletedCounter >=10) break

    if @DeletedCounter % 100 =0  or @DeletedCounter=1 or @DeletedCounter=@DeleteN
    begin
      print('Del:[' + CONVERT(nvarchar(64),@DeletedCounter) + '/' + CONVERT(nvarchar(64),@DeleteN) + ':' + Str((100.00*@DeletedCounter/@DeleteN),6,2)  + '%]' +
           ' Process:[' + @ProcessPacketName + ']' +
           ' Object:[' + @ObjectCliName + ']'
           )
    end

    DELETE
    FROM
      dbo.JobControl_ObjectsProcessData_tbl
    WHERE
     dbo.JobControl_ObjectsProcessData_tbl.ID = @Id

  end
close DeleteTblCursor
deallocate DeleteTblCursor


Print ''
Print '#Step: Cleanup Non Referenced ProcessData Results (for example Process Data of Object Data has been deleted)'
DELETE
FROM
  dbo.JobControl_ObjectsProcessData_tbl
WHERE
  dbo.JobControl_ObjectsProcessData_tbl.ID IN
  (SELECT dbo.JobControl_ObjectsProcessData_tbl.ID FROM dbo.JobControl_ObjectsProcessData_tbl
   LEFT OUTER JOIN dbo.Objects_tbl ON (dbo.JobControl_ObjectsProcessData_tbl.Objects_tbl_ID = dbo.Objects_tbl.Id)
   WHERE dbo.Objects_tbl.Id IS NULL)

GO

Print ''
Print '---------------------------------------------------------'
Print ' Script executed'
Print '---------------------------------------------------------'
GO
