Installation and Technical Issues

[jpendleton]

We are about to upgrade from 7.05 to 7.4 - in a test environment first.  There are a whole bunch of records in the BatchLog that are identified as open using the script in Resolution ID 909.  They do not appear to the Users.  There were some orphaned records in the pending tables but still have many open batches especially in CM.  They mostly have a posting status of 4.

Any ideas about how to clean them out.  Many have been there through past upgrades, we haven't run a test yet, but I'd like to clean up any old "garbage" that is out there.  There are a few for 2011 and that is concerning.

Thank You.

[jnoll]

HUGE DISCLAIMER! I am not condoning nor encouraging anyone to do this. I am stating this should only be done with significant backups and in a TEST environment for extreme scrutinizing of the resutls.

PROCEED WITH CAUTION!

-- How to view these records
select * from tciBatchLog 
where Status <> 6 and PostStatus not in (500,999) and 
ISNULL(BatchCmnt,0) not like '%Permanent%' and
ISNULL(BatchCmnt,0) not like '%System Batch%' and
left(BatchID,2) <> 'MF'


-- Possible cleanup scripts (USE WITH EXTREME CAUTION!!!!!)
delete from tciBatchLog 
where Status <> 6 and PostStatus not in (500,999) and 
ISNULL(BatchCmnt,0) not like '%Permanent%' and
ISNULL(BatchCmnt,0) not like '%System Batch%' and
left(BatchID,2) <> 'MF'
 
delete from timPosting where BatchKey in (select BatchKey from tciBatchLog 
where Status <> 6 and PostStatus not in (500,999) and 
ISNULL(BatchCmnt,0) not like '%Permanent%' and
ISNULL(BatchCmnt,0) not like '%System Batch%' and
left(BatchID,2) <> 'MF')
 
delete from timPostingAcct where imPostingKey in (select imPostingKey from timPosting where BatchKey in (select BatchKey from tciBatchLog 
where Status <> 6 and PostStatus not in (500,999) and 
ISNULL(BatchCmnt,0) not like '%Permanent%' and
ISNULL(BatchCmnt,0) not like '%System Batch%' and
left(BatchID,2) <> 'MF'))
 
delete from tciErrorLog where BatchKey in (select BatchKey from tciBatchLog 
where Status <> 6 and PostStatus not in (500,999) and 
ISNULL(BatchCmnt,0) not like '%Permanent%' and
ISNULL(BatchCmnt,0) not like '%System Batch%' and
left(BatchID,2) <> 'MF')
 
delete from tcmBankStatement where BatchKey in (select BatchKey from tciBatchLog 
where Status <> 6 and PostStatus not in (500,999) and 
ISNULL(BatchCmnt,0) not like '%Permanent%' and
ISNULL(BatchCmnt,0) not like '%System Batch%' and
left(BatchID,2) <> 'MF')
delete from tcmBatch where BatchKey in (select BatchKey from tciBatchLog 
where Status <> 6 and PostStatus not in (500,999) and 
ISNULL(BatchCmnt,0) not like '%Permanent%' and
ISNULL(BatchCmnt,0) not like '%System Batch%' and
left(BatchID,2) <> 'MF')
 
delete from tcmCashTran where CashTranKey in (select cashtrankey from tcmBankStatement where BatchKey in (select BatchKey from tciBatchLog 
where Status <> 6 and PostStatus not in (500,999) and 
ISNULL(BatchCmnt,0) not like '%Permanent%' and
ISNULL(BatchCmnt,0) not like '%System Batch%' and
left(BatchID,2) <> 'MF'))
 
delete from tarPendInvoice where BatchKey in (select BatchKey from tciBatchLog 
where Status <> 6 and PostStatus not in (500,999) and 
ISNULL(BatchCmnt,0) not like '%Permanent%' and
ISNULL(BatchCmnt,0) not like '%System Batch%' and
left(BatchID,2) <> 'MF')
 
delete from tapPendVoucher where BatchKey in (select BatchKey from tciBatchLog 
where Status <> 6 and PostStatus not in (500,999) and 
ISNULL(BatchCmnt,0) not like '%Permanent%' and
ISNULL(BatchCmnt,0) not like '%System Batch%' and
left(BatchID,2) <> 'MF')
 
delete from tapPendVendPmt where BatchKey in (select BatchKey from tciBatchLog 
where Status <> 6 and PostStatus not in (500,999) and 
ISNULL(BatchCmnt,0) not like '%Permanent%' and
ISNULL(BatchCmnt,0) not like '%System Batch%' and
left(BatchID,2) <> 'MF')
 
delete from tcmPendCashTran where BatchKey in (select BatchKey from tciBatchLog 
where Status <> 6 and PostStatus not in (500,999) and 
ISNULL(BatchCmnt,0) not like '%Permanent%' and
ISNULL(BatchCmnt,0) not like '%System Batch%' and
left(BatchID,2) <> 'MF')
delete from tglPosting where BatchKey in (select BatchKey from tciBatchLog 
where Status <> 6 and PostStatus not in (500,999) and 
ISNULL(BatchCmnt,0) not like '%Permanent%' and
ISNULL(BatchCmnt,0) not like '%System Batch%' and
left(BatchID,2) <> 'MF')
delete from timPendInvtTran where BatchKey in (select BatchKey from tciBatchLog 
where Status <> 6 and PostStatus not in (500,999) and 
ISNULL(BatchCmnt,0) not like '%Permanent%' and
ISNULL(BatchCmnt,0) not like '%System Batch%' and
left(BatchID,2) <> 'MF')
 
delete from tciErrorLog where BatchKey in (select BatchKey from tciBatchLog 
where Status <> 6 and PostStatus not in (500,999) and 
ISNULL(BatchCmnt,0) not like '%Permanent%' and
ISNULL(BatchCmnt,0) not like '%System Batch%' and
left(BatchID,2) <> 'MF')

[jpendleton]

Thank You so much.  I was able to clean up 1100 invalid records.  I am left with 21 records that still have issues.  They all show a Posting Error, but won't delete from the BatchLog, which is probably correct. 

They still don't appear to the users from the Client App, and they haven't complained about any problems, so I'm thinking there is a problem now with the Status flags....

But, at least the "garbage" is gone.