The Sage 500 Database Upgrade Utility checks for unposted batches before the upgrade actually begins.  All batches in all companies must be posted or deleted to get past the message informing you that you have unposted batches.

The T-SQL query I modified will help you find these open batches earlier in the upgrade process.  The first T-SQL query is the actual one that is executed near the beginning of the Sage 500 upgrade. This query returns the Companyid, Module, and number of batches in the combination that show as unposted.

SELECT SourceCompanyID, substring(BatchID, 1, 2), count(*) from tciBatchLog

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**%') or

(BatchID like 'SB%' and BatchKey in (select BatchKey from tarPendInvoice)))

group by  SourceCompanyID, substring(BatchID, 1,2)

order by 1,2

Notes about the above query:

If the Status is '6', that means posted. When the PostStatus is 500 , that means posting status is completed. If its 999, then the batch was deleted. The batchid is composed of 3 fields, module id, batch type id, and batch number

When testing a Sage 500 upgrade, one of the first things to run is the above query. It will return the list  the run the above query to see what open batches are listed. I made a change to the above query and added Batchid. This will help identify the entire batchid instead of the two-character batchid.  Here is the query as it looks now:

SELECT Batchid, SourceCompanyID, substring(BatchID, 1, 2) from tciBatchLog

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**%') or

(BatchID like 'SB%' and BatchKey in (select BatchKey from tarPendInvoice)))

group by Batchid, SourceCompanyID, substring(BatchID, 1,2)

order by 1,2,3

Some of the batches are easier to identify than others. If you aren't sure which module they are from, look at the definition of the batch types. Go to the Setup XX options task for the give moduleid. 

For example, let's say one of the batches returned by the query is CMRE-0000373.  We know it is a Cash Management batch(CM) but what is the batch id RE?

1. Go to Cash Management, Maintenance, Set up CM Options
2. Click on the Batches.. button which will bring up the Customize Batches screen. Look up the BatchID and we find that its a CM Bank Reconciliation type of batch.
3. Go to Cash Management, Activities, Reconcile Bank Statements, and pull up batch CMRE-0000373
4. Continue and complete the posting process or click the X and delete the batch
5. Repeat the process for each batch
6. Once al batches have been processed, re-run the T-SQL query and it should return zero rows.

I hope this information helps save time during your upgrade projects. For additional information, check the Sage 500 customer/partner portal and search for knowledgebase id 17756(Error: "The database upgrade cannot proceed because there are unposted batches"). The article discusses other information related to troubleshooting