Dynamics SL Batches are Taking a Long Time to Release – What Can I Do?

More by
Dynamics SL Batches taking too long

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 have questions or need some assistance, visit our support page for more help.

  • 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
commit
  • 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.

If you have questions or need some assistance, visit our support page for more help.

Leave a Reply