I’ve had multiple support cases about stuck batches and posting interruptions in Dynamics GP, so I thought I would write an article about the basics of advanced troubleshooting to help others with this issue. This article is broken down into different sections, so you can scroll to the relevant section if you’re in a time crunch.
Reasons & Error Messages
When a transaction or batch is posting, the process can be interrupted due to a variety of reasons; this can occur in many ERP systems and in Dynamics GP.
Some of the common reasons include:
- Temporary connection lost between server and workstation, or terminal server where Dynamics GP is installed.
- Power outage or computer turned off while posting.
- Corruption or damaged code on the Dynamics GP installation on the user’s workstation.
- Corruption or issues with the UserID logged in while posting.
- Corrupt reports or forms dictionaries.
- Error in the transaction.
You’ll need to note if these interruption reasons happen for a specific UserID, or a specific customer master or vendor. Also note if the interruptions occur randomly, periodically, or often. If the interruptions happen quite often, contact us and our experienced technical consultants can help assist to troubleshoot the issue and track down the root cause of the issue.
The typical error messages you’ll receive with stuck batches are:
- Use the batch recovery window to continue processing this batch.
- A batch is held in the Posting, Receiving, Busy, Marked, Locked or Edited status.
- Opening Sales Transaction Entry window – your previous transaction – level posting session has not finished processing.
1. Batch Recovery Window
You’ll need to ensure you have access to this window. If not, your administrator will need to provide you with access or they will need to perform the next few steps.
a. The batch recovery window is located in the menu options: Microsoft Dynamics > Tools > Routines > Batch Recover.
b. It will typically show an error message of the status.
c. We can mark off the batch in question and select ‘continue’. Normally it will do 1 of three things:
- i) Push the batch through the posting.
- ii) Return the batch to the unposted state.
- iii) Loop in the batch recovery window.
For the above i) if the batch posts through, you’ll still need to verify in the inquiry windows to ensure the batch has posted properly and not multiple times into the subledger (AP and AR) and general ledger. If there are issues, you’ll need to void (subledger) or reverse (general ledger) the transaction. This step is extremely important to ensure data integrity.
For the above ii) if the batch is returned to the unposted state, running an edit list will reveal what the error was, some of the typical edit list error messages include:
- Fiscal period close.
- Account missing or missing distributions.
- Account doesn’t balance.
- Security access on accounts.
To print and review an edit list:
- Using the batch window (depending on the type of batch – GL, AP, or AR).
- Select the batch.
- Press the printer icon (usually print to screen will suffice).
- Select ‘edit list’.
The error messages are typically located near the top with details within the report itself for the transaction. They are quite descriptive and will point you in the right direction to isolate the issue.
2. A Batch is Held
A batch is held in the Posting, Receiving, Busy, Marked, Locked or Edited status.
This next error will require SQL database access. Typically, an IT manager or DBA (not the end user) will have access to the SQL database and the SQL Management Studio. Our recommendation is to always back up the database before proceeding and testing the scripts in a refreshed test company. See Dynamics GP – Automatically Update the Test Company with the Live Company Database for this process.
At a minimum, you can run this table back up script to back up individual tables itself (replace the table SY00500 with the table you’ll be modifying).
The script used to back up a single table is:
Use XXX select * into sy00500_BU from sy00500.
Where XXX is the database name Sy00500 is the table in this example, and SY00500_BU can be replaced with the naming convention of your choice e.g. include the date. Our technical experts at Encore can assist in recovering the backed-up data if an error occurs.
The tables involved with clearing stuck batches are:
The table ACTIVITY sits in the DYNAMICS database and tells you which users are logged into Dynamics GP currently.
The table SY00800 in the DYNAMICS database is the batch activity table.
The table SY00801 in the DYNAMICS database is the resource activity table.
The temp tables DEX_LOCK and DEX_SESSION and dexterity locks and sessions table.
The recommendation is to:
1. Back up the database.
2. Get all users to log out of Dynamics GP via the user interface.
3. Run the below select scripts to see if there’s any stuck records or sessions:
- Select * from DYNAMICS..ACTIVITY
- Select * from DYNAMICS..SY00800
- Select * from DYNAMICS..SY00801
- Select * from TEMPDB..DEX_LOCK
- Select * from TEMPDB..DEX_SESSION
4. To delete or remove all records from these tables run the following scripts:
- Delete from DYNAMICS..ACTIVITY
- Delete from DYNAMICS..SY00800
- Delete from DYNAMICS..SY00801
- Delete from TEMPDB..DEX_LOCK
- Delete from TEMPDB..DEX_SESSION
5. Reset the batch status back to ‘0’ and marked to post back to ‘0’.
- Use XXX update SY00500 set MKDTOPST=0, BCHSTTUS=0 where BACHNUMB =’YYYY’.
- Where XXX is the company database and YYYY is the batch in question.
- Here’s a script that removes stuck batch records without having all users logged out.
6. Fixed Assets stuck batch the script to remove the stuck records are:
- Use XXX delete from FA40203.
Once the batch status has been set back to available (0), please continue to print the edit list to review the transaction. Review the inquiry of the subledger (AP, AR account) and the general ledger to see if there’s any entries posted throughout this process.
3. Message When Opening Sales Transaction Entry Window
Opening Sales Transaction Entry window – your previous transaction – level posting session has not finished processing.
Below is another stuck batch/process issue we commonly receive with sales order processing batches; once the previous steps in this blog have been completed.
The scripts to analyze the batches causing the error are:
- Use XXX select * from SY00500 where BACHNUMB=’’
- Use XXX select * from SY00500 where BACHNUMB=’yyy’
- Use XXX select * from SY00500 where BACHNUMB like ‘*Sales%’
- XXX is the company database and yyy is the UserID that’s receiving the above error message
The first step if the above three scripts provides results would be to delete them. Please back up the database or table before proceeding.
The scripts for deleting these records causing the issues are:
- Use XXX delete from SY00500 where BACHNUMB=’’
- Use XXX delete from SY00500 where BACHNUMB=’yyy’
- Use XXX delete from SY00500 where BACHNUMB like ‘*Sales%’
The next step is to locate the corrupt records in the SOP10100 table.
The scripts to validate the records are:
- SELECT BACHNUMB,* from SOP10100 where BACHNUMB = ” SELECT BACHNUMB,* from SOP10100 where BACHNUMB = ‘<xxx>’
- SELECT * FROM SOP10200 where SOPNUMBE in (SELECT SOPNUMBE from SOP10100 where BACHNUMB = ‘xxx’)
- SELECT * FROM SOP10200 where SOPNUMBE in (SELECT SOPNUMBE from SOP10100 where BACHNUMB = ‘ ‘)
- SELECT * FROM SOP10102 where SOPNUMBE in (SELECT SOPNUMBE from SOP10100 where BACHNUMB = ‘xxx’)
- SELECT * FROM SOP10102 where SOPNUMBE in (SELECT SOPNUMBE from SOP10100 where BACHNUMB = ‘ ‘)
Where xxx is the User ID in question.
The scripts to remove the corrupt records are:
- DELETE SOP10200 where SOPNUMBE in (SELECT SOPNUMBE from SOP10100 where BACHNUMB = ‘xxx’)
- DELETE SOP10200 where SOPNUMBE in (SELECT SOPNUMBE from SOP10100 where BACHNUMB = ‘ ‘)
- DELETE SOP10102 where SOPNUMBE in (SELECT SOPNUMBE from SOP10100 where BACHNUMB = ‘xxx’)
- DELETE SOP10102 where SOPNUMBE in (SELECT SOPNUMBE from SOP10100 where BACHNUMB = ‘ ‘)
The next steps would be to run Check Links on the Sales Work table:
- In Microsoft Dynamics GP, point to Maintenance on the Microsoft Dynamics GP menu, and then click Check Links.
- In the Series list, click Sales, and then run the Check Links procedure on the Sales Work table group.
After removing the stuck batch, it’s important to verify the transactions at the general ledger and subledger level; some might have posted partially through the user interface and void or reverse any transactions with issues.
Further advance troubleshooting can be done with the following scripts:
1. All Receivables/Payables Scripts (Contact Encore for these scripts)
- For an invoice, payment, debit note, or credit note, the resulting record will sit in the receivable tables, general ledger, multi-currency, and bank reconciliation tables.
- It will allow you to determine if there are duplicate records sitting in multiple tables e.g. sitting in open and historical tables.
2. Linked is another useful script/stored procedure for searching for any record in the company database and it shows you the tables the record is in.
If the above three scripts are used, always back up and verify on an updated test database before removing records.
Contact us if you have any questions.
What is new in Dynamics GP 2018?
Get 9 premium pieces of content that will help you plan a Dynamics GP upgrade!Get The Upgrade Guide