I recently had a customer call me and describe that their batches were taking a long time to release in Microsoft Dynamics SL. They have Cash Manager installed and configured properly, but any batches in Cash Manager, Check, or AP Payments, were taking forever to release.
They even tried to cancel the release and redo it multiple times. No success.
How to fix this?
The most likely cause is a batch in Cash Manager with a period set many years into the future. This causes a large number of records to be created in the cashavgd table. This table is updated when a batch is released.
Follow the instructions below to identify and fix the data affecting the batch release.
If you need assistance, email us at email@example.com and we can help with this process.
- You’ll need access to SQL Management studio.
- Run the following query:
select * from cashavgd order by pernbr desc
- The statement in step 2 will show all records in the table in order of highest pernbr.
- Determine the latest period that should appear in the table. This would be the current period from any of the modules that impact cash manager.
- Run the following query: (replacing yyyymm with the current period)
select * from cashavgd where pernbr > ‘yyyymm’
This will show the records that need to be deleted. Make note of the number of records returned.
- Make a backup of the database in case you need to restore.
- Run the following delete statement: (replacing yyyymm with the appropriate period)
begin transaction delete cashavgd where pernbr > ‘yyyymm’
- Once you verify that the records deleted matches the number of records from step 5 run the following SQL statement
- You should determine the batch that was initially released with the future period that caused the error. Run the following SQL statement: (replacing yyyymm with the current period)
select * from batch where status = ‘U’ and perpost > ‘yyyymm’
- Review the data returned from step 9 to see which batch is causing the issue. Since the batch has been released and is sitting in an unposted status, you’ll need to determine how to fix this issue. It is not recommended you delete the batch.
Be warned that when using a SQL command of ‘begin transaction’ it’s important to issue a ‘commit’ or ‘rollback’ as soon as possible since tables and records could become locked and other user processes could be at risk of completing in a timely manner.
For assistance, email our team of Dynamics SL & SQL experts at firstname.lastname@example.org.