During a database upgrade, the database upgrade utility will stop and indicate if there are open batches and what modules they reside in. However, it does not give any specifics of what those batches are, which requires the individual upgrading the database to identify them and post/delete the batches before they can upgrade the MAS 500 database.
To assist in upgrading the database, the Database upgrade utility should be modified to provide a detail report of the open batches that need to be closed to help expedite the process.
by: Louis D. | over a year ago | 6 - General Enhancements
Comments
I totally agree. We had some batches in the last upgrade that could not be identified so that we could remedy them prior to the upgrade process. We have not altered the standard Sage batch naming conventions.
Let's take it a step further, and have the database upgrade utility leave open batches open. Forget about identifying them, they shouldn't be required to be closed.
I agree Chris. If a back up is done, prior to the upgrade, then it shouldn't matter if batches are open or not as you can restore from the back up if a problem occurs and start again.
From our DBA Tom S:
If you are are refering to the SB batch, that is the system batch that is generated if there are records in tarPendInvoice which are Posted shipments that have not been invoiced. Invoice these posted shipments and the SB batch should go away. We used these queries to inform our users what to clear up:
select distinct SourceCompanyID as Company, substring(BatchID, 1, 2) as Module,tsmLocalString.LocalText as 'Batch Type', Batchid,OrigUserID as CreateUser, CreateDate from tciBatchLog
join tciBatchType ON tciBatchLog.BatchType = tciBatchType.BatchType
join tsmLocalString ON tciBatchType.BatchDescStrNo = tsmLocalString.StringNo
Where SourceCompanyID not in ('CAD', 'COA', 'CON', 'DKS', 'SGE', 'SLS', 'SOA') and
((Status <> 6 And PostStatus NOT IN (500, 510, 999) and BatchID not like 'SB%' and BatchID not like 'GL**%' and BatchID not like 'SO**%' and BatchID not like 'MF**%') or
(BatchID like 'SB%' and BatchKey in (select BatchKey from tarPendInvoice)))
and (BatchKey IN (SELECT BatchKey from tciBatchTran WHERE TranCount > 0) OR tciBatchLog.BatchType IN (404,504,702,9000,9001,9002,9003)
OR EXISTS (SELECT 1 from tciBatchTranType bt WHERE tciBatchType.BatchType = bt.BatchType AND bt.TranType >= 10000))
order by SourceCompanyID, substring(BatchID, 1, 2)
select SourceCompanyID, substring(BatchID, 1, 2), count(*) from tciBatchLog
join tciBatchType ON tciBatchLog.BatchType = tciBatchType.BatchType
Where SourceCompanyID not in ('CAD', 'COA', 'CON', 'DKS', 'SGE', 'SLS', 'SOA') and
((Status <> 6 And PostStatus NOT IN (500, 510, 999) and BatchID not like 'SB%' and BatchID not like 'GL**%' and BatchID not like 'SO**%' and BatchID not like 'MF**%') or
(BatchID like 'SB%' and BatchKey in (select BatchKey from tarPendInvoice)))
and (BatchKey IN (SELECT BatchKey from tciBatchTran WHERE TranCount > 0) OR tciBatchLog.BatchType IN (404,504,702,9000,9001,9002,9003)
OR EXISTS (SELECT 1 from tciBatchTranType bt WHERE tciBatchType.BatchType = bt.BatchType AND bt.TranType >= 10000))
group by SourceCompanyID, substring(BatchID, 1, 2)
order by 1, 2
Also...we use service management...if you have any batches that are open before the upgrade...they will all get set to deleted. If you have hit the warning screen as part of the upgrade that says that there are open batches, it's TOO LATE...your SRO batches have already been set to deleted.
This issue is scheduled to appear in the 2023 version of Sage 500 ERP. The upgrade process for 2023 will show the first 12 or so batches holding up the upgrade, the database upgrade log will have all the individual batches blocking the upgrade. Going forward, a BIE has been added that can identify the individual batches prior to upgrading again. All queries will ignore demo data companies.