We receive several calls from our customers where they have a “Stuck Batch”. This can happen for instance if you are updating a batch and it doesn’t complete and you get a message to use Batch Recovery.
There are times that running Batch Recovery doesn’t work, resulting in a ‘Stuck Batch’.
When this happens it is recommended to get everyone out of GP to run SQL scripts that will clear files that are related to the ‘Stuck Batch’.
Below is the Knowledgebase Article which can be found at: http://support.microsoft.com/kb/850289
From SQL Server Management Studio
To run the script, follow these steps:
- Make sure that you have a current backup of the company database, and ask all users to exit Microsoft Dynamics GP. To create the backup in Microsoft Dynamics GP, follow the appropriate steps after all users log off from Microsoft Dynamics GP:
- On the File menu, click Backup.
- In the Company Name list, click the company that you want to back up.
- In the Select the backup file box, click the yellow folder to open the location in which you want to put the backup file.
- In the Object Explorer, Expand your databases so you see the database you want to back up.
- Right click the Database Name, go to Tasks, and select Backup.
- Click the add button and select the location and file name you wish to save your backup to.
- Click Ok to start the backup.
- View the contents of the following tables to verify that all users are logged off: DYNAMICS..ACTIVITY, DYNAMICS..SY00800, DYNAMICS..SY00801, TEMPDB..DEX_LOCK, and TEMPDB..DEX_SESSION. To do this, run the following script.
- SELECT * FROM DYNAMICS..ACTIVITY
- SELECT * FROM DYNAMICS..SY00800
- SELECT * FROM DYNAMICS..SY00801
- SELECT * FROM TEMPDB..DEX_LOCK
SELECT * FROM TEMPDB..DEX_SESSION
Note When all users are logged off from Microsoft Dynamics GP, these tables will not have any records in them.
- If no results are returned, go to ‘Step 4’. Otherwise, clear the stuck records by using any of the following appropriate scripts.
- DELETE DYNAMICS..ACTIVITY
- DELETE DYNAMICS..SY00800
10. DELETE DYNAMICS..SY00801
11. DELETE TEMPDB..DEX_LOCK
Now you need to reset the Batch Status to remove it from Batch Recovery.
Run the following script against the company database. Replace XXX with the batch number or the name of the batch that you are trying to post or select in Microsoft Dynamics GP.
UPDATE SY00500 SET MKDTOPST=0, BCHSTTUS=0 where BACHNUMB=’XXX’
Note The value of BACHNUMB is the same as the value of the Batch ID window in Microsoft Dynamics GP.
- Verify the accuracy of the transactions.
(Re-print the edit list and correct any errors)
Verify that you can edit and post the batches.
There will be times when you need to clear a batch and it is not possible or feasible to have everyone exit GP. If that is the case, verify that no user is in the ‘Stuck’ batch and run the following Script.
From SQL Server Management Studio
Against the company database, run the following:
SELECT MKDTOPST, BCHSTTUS, * FROM SY00500 where BACHNUMB = ‘xxxx’
(Replace xxx with the batch ID)
Once you’ve located the batch to be corrected, you can run this update statement:
UPDATE SY00500 SET MKDTOPST=0, BCHSTTUS=0 WHERE BACHNUMB=’xxxx’
(Replace xxx with the actual batch ID)
Once the Update statement has completed successfully you will no longer see the batch under Batch Recovery. You will now be able to access the batch in the original module. It is now recommended that you re-print the edit list and correct any errors in the batch.
Caution: If you decide to try posting the batch again without reviewing for errors, you risk having the batch posting fail again and becoming ‘Stuck’ again.
It is always recommended after a batch posting fails to do the following:
- Review the edit list and correct any errors listed.
- Review transactions from the batch to see if any have already been updated correctly in the sub-ledger.
- Review transactions from the batch to see if they have already updated the General Ledger.
If you have transactions from the batch that have successfully posted to both the sub-ledger and General Ledger, you should delete the transaction from the batch.
If you have transactions that updated to the sub-ledger but not to the GL, you may consider voiding the transaction in the sub-ledger, then re-enter/post in order to take advantage of the drill back functionality from GL to the sub-ledger.
Once you have corrected any errors in the batch and removed any records that already posted, you should be able to post the batch successfully, without errors.
Please contact us at firstname.lastname@example.org for assistance with resolving a gp stuck batch in dynamics.