Print ''
Print '-------------------------------------------------------------------------'
Print ' Cleanup ProcessData'
Print ' Database.....: #DB_N#'
Print ' Autor........: J.Feuerstein'
Print ' FirstVersion.: 17.12.2012'
Print ' LastVersion..: 04.11.2015'
Print '-------------------------------------------------------------------------'
GO
use [#DB_N#]


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=''


print('Clean up in DB.............: ' + '#DB_N#')



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


print('Non Last Process Results N.: [' + CONVERT(nvarchar(64),@DeleteN) + ']')

-- create Curor
declare DeleteTblCursor cursor fast_forward for
select Id, ProcessPacketName
FROM
  dbo.JobControl_ObjectsProcessData_tbl
WHERE
  dbo.JobControl_ObjectsProcessData_tbl.Row_isLastRes <> 1


set nocount on
open DeleteTblCursor
 -- alle Objekte

 while 1=1
  begin
    SET @DeletedCounter = @DeletedCounter + 1
    fetch next from DeleteTblCursor into @Id, @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 + ']'
           )
    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: START'
Print '       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)

Print '#Step: Cleanup Non Referenced ProcessData Results: DONE'
Print '.........................................................................'

GO

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