Ask Your Question

Revision history  [back]

Revision 9
Ahmed Farouk's avatar
81
Ahmed Farouk
updated 2021-07-27 00:52:28 +0200

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.namasoft.erp.gui.server.DeleteFromFile-e:/rc/delete.txt,e:/rc/donedelete.txt,e:/rc/deleteerrors.txt&gui=true

4- Monitor the task from Monitor Current Tasks link, OR check both donedelete & deleteerrors text files:
- http://localhost:6060/erp/utils?monitortasks=true
- 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

Revision 8
Ahmed Farouk's avatar
81
Ahmed Farouk
updated 2021-07-26 23:55:41 +0200

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.namasoft.erp.gui.server.DeleteFromFile-e:/rc/delete.txt,e:/rc/donedelete.txt,e:/rc/deleteerrors.txt&gui=true

4- Monitor the task from Monitor Current Tasks link, OR check both donedelete & deleteerrors text files:
- http://localhost:6060/erp/utils?monitortasks=true
- 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

Revision 7
Ahmed Farouk's avatar
81
Ahmed Farouk
updated 2021-07-26 23:49:30 +0200

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.namasoft.erp.gui.server.DeleteFromFile-e:/rc/delete.txt,e:/rc/donedelete.txt,e:/rc/deleteerrors.txt&gui=true

4- Monitor the task from Monitor Current Tasks link, OR check both donedelete & deleteerrors text files:
- http://localhost:6060/erp/utils?monitortasks=true
- 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 query, then review results with entityType name with needed entityType names.names..
    update *entityType name* set attachment1_id = null
    null<br/> update *entityType name* set attachment2_id = null
    null<br/> update *entityType name* set attachment3_id = null
    null<br/> update *entityType name* set attachment4_id = null
    null<br/> 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

Revision 6
Ahmed Farouk's avatar
81
Ahmed Farouk
updated 2021-07-25 17:32:48 +0200

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.namasoft.erp.gui.server.DeleteFromFile-e:/rc/delete.txt,e:/rc/donedelete.txt,e:/rc/deleteerrors.txt&gui=true

4- Monitor the task from Monitor Current Tasks link, OR check both donedelete & deleteerrors text files:
- http://localhost:6060/erp/utils?monitortasks=true
- 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, then review results with entityType names..
    update *entityType name* set attachment1_id = null<br/> update *entityType name* set attachment2_id = null<br/> update *entityType name* set attachment3_id = null<br/> update *entityType name* set attachment4_id = null<br/> 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

Revision 5
Ahmed Farouk's avatar
81
Ahmed Farouk
updated 2021-07-25 17:04:14 +0200

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.namasoft.erp.gui.server.DeleteFromFile-e:/rc/delete.txt,e:/rc/donedelete.txt,e:/rc/deleteerrors.txt&gui=true

4- Monitor the task from Monitor Current Tasks link, OR check both donedelete & deleteerrors text files:
- http://localhost:6060/erp/utils?monitortasks=true
- 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

  • 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

Revision 4
Ahmed Qasid's avatar
5.8k
Ahmed Qasid
updated 2021-07-24 13:28:20 +0200

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.namasoft.erp.gui.server.DeleteFromFile-e:/rc/delete.txt,e:/rc/donedelete.txt,e:/rc/deleteerrors.txt&gui=true

4- Monitor the task from Monitor Current Tasks link, OR check both donedelete & deleteerrors text files:
- http://localhost:6060/erp/utils?monitortasks=true
- 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''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

  • 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

Revision 3
Ahmed Qasid's avatar
5.8k
Ahmed Qasid
updated 2021-07-24 13:26:20 +0200

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/> '20210101'
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.namasoft.erp.gui.server.DeleteFromFile-e:/rc/delete.txt,e:/rc/donedelete.txt,e:/rc/deleteerrors.txt&gui=true

4- Monitor the task from Monitor Current Tasks link, OR check both donedelete & deleteerrors text files:
- http://localhost:6060/erp/utils?monitortasks=true
- 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    '20210101'

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

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

Revision 2
Ahmed Farouk's avatar
81
Ahmed Farouk
updated 2021-07-17 17:51:51 +0200

1- Run the following query in SQL Server
select targetType,targetId,code from EntitySystemEntry
where isMasterFile = 0 and generationType = 'NotGenerated' and valueDate < '20210101'
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.namasoft.erp.gui.server.DeleteFromFile-e:/rc/delete.txt,e:/rc/donedelete.txt,e:/rc/deleteerrors.txt&gui=true

4- Monitor the task from Monitor Current Tasks link, OR check both donedelete & deleteerrors text files:
- http://localhost:6060/erp/utils?monitortasks=true
- 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
    where fromid is not null and valueDate < '20210101'

  • Fix attachments delete error (when deleting a report, database error occurred)
    while(exists(select * from INFORMATIONSCHEMA.TABLECONSTRAINTS t left join INFORMATIONSCHEMA.KEYCOLUMNUSAGE k on k.CONSTRAINTNAME = t.CONSTRAINTNAME where CONSTRAINTTYPE='FOREIGN KEY' and COLUMNNAME in ('attachmentid','attachment1id','attachment2id','attachment3id','attachment4id','attachment5id','attachment6id','mainFileid'))) begin declare @sql nvarchar(2000) SELECT TOP 1 @sql=('ALTER TABLE ' + t.TABLESCHEMA + '.[' + t.TABLENAME + '] DROP CONSTRAINT [' + t.CONSTRAINTNAME + ']') from INFORMATIONSCHEMA.TABLECONSTRAINTS t left join INFORMATIONSCHEMA.KEYCOLUMNUSAGE k on k.CONSTRAINTNAME = t.CONSTRAINTNAME where CONSTRAINTTYPE='FOREIGN KEY' and COLUMNNAME in ('attachmentid','attachment1id','attachment2id','attachment3id','attachment4id','attachment5id','attachment6id','mainFile_id') begin transaction x exec (@sql) print @sql commit transaction x end

  • Allow Deleting Approval Cases
    while(exists(select * from INFORMATIONSCHEMA.TABLECONSTRAINTS t left join INFORMATIONSCHEMA.KEYCOLUMNUSAGE k on k.CONSTRAINTNAME = t.CONSTRAINTNAME where CONSTRAINTTYPE='FOREIGN KEY' and COLUMNNAME in ('currentApprovalCaseid'))) begin declare @sql nvarchar(2000) SELECT TOP 1 @sql=('ALTER TABLE ' + t.TABLESCHEMA + '.[' + t.TABLENAME + '] DROP CONSTRAINT [' + t.CONSTRAINTNAME + ']') from INFORMATIONSCHEMA.TABLECONSTRAINTS t left join INFORMATIONSCHEMA.KEYCOLUMNUSAGE k on k.CONSTRAINTNAME = t.CONSTRAINTNAME where CONSTRAINTTYPE='FOREIGN KEY' and COLUMNNAME in ('currentApprovalCase_id') exec (@sql) end

Revision 1
Ahmed Farouk's avatar
81
Ahmed Farouk
answered 2021-07-17 17:40:08 +0200

1- Run the following query in SQL Server

select targetType,targetId,code from EntitySystemEntry
where isMasterFile = 0 and generationType = 'NotGenerated' and valueDate < '20210101'
order by valueDate desc,creationDate desc

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

3- Run the following command in your server web browser
http://localhost:8080/erp/test?util=com.namasoft.erp.gui.server.DeleteFromFile-e:/rc/delete.txt,e:/rc/donedelete.txt,e:/rc/deleteerrors.txt&gui=true

4- Monitor the task from Monitor Current Tasks link, OR check both donedelete & deleteerrors text files:
- http://localhost:6060/erp/utils?monitortasks=true
- 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
    where fromid is not null and valueDate < '20210101'

  • Fix attachments delete error (when deleting a report, database error occurred)
    while(exists(select * from INFORMATIONSCHEMA.TABLECONSTRAINTS t left join INFORMATIONSCHEMA.KEYCOLUMNUSAGE k on k.CONSTRAINTNAME = t.CONSTRAINTNAME where CONSTRAINTTYPE='FOREIGN KEY' and COLUMNNAME in ('attachmentid','attachment1id','attachment2id','attachment3id','attachment4id','attachment5id','attachment6id','mainFileid'))) begin declare @sql nvarchar(2000) SELECT TOP 1 @sql=('ALTER TABLE ' + t.TABLESCHEMA + '.[' + t.TABLENAME + '] DROP CONSTRAINT [' + t.CONSTRAINTNAME + ']') from INFORMATIONSCHEMA.TABLECONSTRAINTS t left join INFORMATIONSCHEMA.KEYCOLUMNUSAGE k on k.CONSTRAINTNAME = t.CONSTRAINTNAME where CONSTRAINTTYPE='FOREIGN KEY' and COLUMNNAME in ('attachmentid','attachment1id','attachment2id','attachment3id','attachment4id','attachment5id','attachment6id','mainFile_id') begin transaction x exec (@sql) print @sql commit transaction x end

  • Allow Deleting Approval Cases
    while(exists(select * from INFORMATIONSCHEMA.TABLECONSTRAINTS t left join INFORMATIONSCHEMA.KEYCOLUMNUSAGE k on k.CONSTRAINTNAME = t.CONSTRAINTNAME where CONSTRAINTTYPE='FOREIGN KEY' and COLUMNNAME in ('currentApprovalCaseid'))) begin declare @sql nvarchar(2000) SELECT TOP 1 @sql=('ALTER TABLE ' + t.TABLESCHEMA + '.[' + t.TABLENAME + '] DROP CONSTRAINT [' + t.CONSTRAINTNAME + ']') from INFORMATIONSCHEMA.TABLECONSTRAINTS t left join INFORMATIONSCHEMA.KEYCOLUMNUSAGE k on k.CONSTRAINTNAME = t.CONSTRAINTNAME where CONSTRAINTTYPE='FOREIGN KEY' and COLUMNNAME in ('currentApprovalCase_id') exec (@sql) end