How to bulk delete documents created by system users?

edit

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