0

How to bulk delete documents created by system users?

bulk delete \ delete all documents created by system users within a specific period.

Ahmed Farouk's avatar
81
Ahmed Farouk
asked 2021-07-17 17:13:43 +0200
edit flag offensive 0 remove flag close merge delete

Comments

add a comment see more comments

1 Answer

0

1- Run the following query in SQL Server
select targetType,targetId,code from EntitySystemEntry <br/> where isMasterFile = 0 and generationType = 'NotGenerated' and valueDate < '20210101'<br/> order by valueDate desc,creationDate desc

2- Copy results rows from SQL to text file e:/rc/delete.txt

3- Run the following command in your server web browser
http://localhost:8080/erp/test?util=com…

4- Monitor the task from Monitor Current Tasks link, OR check both donedelete & deleteerrors text files:
- http://localhost:6060/erp/utils?monitor…
- e:/rc/donedelete.txt
- e:/rc/deleteerrors.txt

Notes:
You might need to run the following useful queries before doing previous steps ..

  • UNLINK ALL FROM DOCS
    update EntitySystemEntry set fromType = null, fromId = null <br/> where fromid is not null and valueDate < '20210101'

  • Fix attachments delete error (when deleting a report, database error occurred)
    while(exists(select * from INFORMATION_SCHEMA.TABLE_CONSTRAINTS t left join INFORMATION_SCHEMA.KEY_COLUMN_USAGE k on k.CONSTRAINT_NAME = t.CONSTRAINT_NAME where CONSTRAINT_TYPE='FOREIGN KEY' and COLUMN_NAME in ('attachment_id','attachment1_id','attachment2_id','attachment3_id','attachment4_id','attachment5_id','attachment6_id','mainFile_id'))) begin declare @sql nvarchar(2000) SELECT TOP 1 @sql=('ALTER TABLE ' + t.TABLE_SCHEMA + '.[' + t.TABLE_NAME + '] DROP CONSTRAINT [' + t.CONSTRAINT_NAME + ']') from INFORMATION_SCHEMA.TABLE_CONSTRAINTS t left join INFORMATION_SCHEMA.KEY_COLUMN_USAGE k on k.CONSTRAINT_NAME = t.CONSTRAINT_NAME where CONSTRAINT_TYPE='FOREIGN KEY' and COLUMN_NAME in ('attachment_id','attachment1_id','attachment2_id','attachment3_id','attachment4_id','attachment5_id','attachment6_id','mainFile_id') begin transaction x exec (@sql) print @sql commit transaction x end

  • Fix LargeData delete Error
    Run the following query after replacing entityType name with needed entityType names.
    update *entityType name* set attachment_id = null
    update *entityType name* set attachment1_id = null
    update *entityType name* set attachment2_id = null
    update *entityType name* set attachment3_id = null
    update *entityType name* set attachment4_id = null
    update *entityType name* set attachment5_id = null

  • Allow Deleting Approval Cases
    while(exists(select * from INFORMATION_SCHEMA.TABLE_CONSTRAINTS t left join INFORMATION_SCHEMA.KEY_COLUMN_USAGE k on k.CONSTRAINT_NAME = t.CONSTRAINT_NAME where CONSTRAINT_TYPE='FOREIGN KEY' and COLUMN_NAME in ('currentApprovalCase_id'))) begin declare @sql nvarchar(2000) SELECT TOP 1 @sql=('ALTER TABLE ' + t.TABLE_SCHEMA + '.[' + t.TABLE_NAME + '] DROP CONSTRAINT [' + t.CONSTRAINT_NAME + ']') from INFORMATION_SCHEMA.TABLE_CONSTRAINTS t left join INFORMATION_SCHEMA.KEY_COLUMN_USAGE k on k.CONSTRAINT_NAME = t.CONSTRAINT_NAME where CONSTRAINT_TYPE='FOREIGN KEY' and COLUMN_NAME in ('currentApprovalCase_id') exec (@sql) end

  • Allow Deleting Revision Cases
    Run the following query, then review results with entityType names..
    with data as ( select distinct targetType from EntitySystemEntry where revised = 1 ) select 'update '+targetType+' set primitiveValue = 0 where primitiveValue = 1' from data
    Run the following query after replacing entityType name with copied entityType names..
    update *entityType name* set primitiveValue = 0

Ahmed Farouk's avatar
81
Ahmed Farouk
answered 2021-07-17 17:40:08 +0200, updated 2021-07-27 00:52:28 +0200
edit flag offensive 0 remove flag delete link

Comments

add a comment see more comments

Your Answer

Login/Signup to Answer