Stuck Batches & Posting Interruptions in Dynamics GP

Have you ever attempted to post a batch in Dynamics GP, only to receive one of the following error messages?

• A batch is held in the Posting, Receiving, Busy, Marked, Locked or Edited status.
• Batch Failed to Complete Posting. Use the Batch Recovery Window to complete the posting process.
• Your previous transaction-level posting session has not finished processing.

If you regularly post in GP, then chances are you have had the unfortunate experience of one not going as planned. After receiving the error, the batch will usually be stuck in the status of: Printing, Edit Required, Posting Interrupted, Remittance Processing, etc. This article will help provide you with a basic understanding of what causes and how to resolve this issue.

Cause

While this can be caused by various reasons, below are some of the most common:

• Network Interruption
  • Temporary Loss of Internet Connection
  • Workstation or Terminal Server Loses Connection to SQL Server
• Power Outage
• Corrupt or Duplicate SQL Records
• Transaction Errors
  • Closed Fiscal Periods
  • Unmatched Distributions
  • Missing Required Information
• Insufficient User Permissions

Network and Power Outages

When Dynamics GP posts transactions, it first copies the SQL records from its current table (Work or Open), pastes them into the next table in the process (Open or History), then goes back and deletes the records from the initial table. It does this so, in the event of a posting interruption, transactions are not completely deleted. Instead, there is the potential for duplicate records to be left in SQL. When this happens, the duplicates need to be removed before the batch can continue processing.

Transaction Errors

Certain errors in the transaction can cause the batch to crash as well. For example, let’s say you created a batch with a posting date of May 30^th, but you don’t post it until June 6^th. If the Fiscal Period for June has been closed, the batch will crash, and the period will need to be opened. Or, if the transaction distributions were saved with errors and do not balance, you will have the same result.

User Permissions

In SQL, there is a security role called DYNGRP which grants permission to all objects. Users created within GP are assigned to this group as well. Sometimes the user can become disconnected from the DYNGRP so, when posting, the batch will crash because the user is missing permissions. This is a less likely scenario but one that can still happen.

Resolutions

Method 1- Batch Recovery Window

The Batch Recovery window is located in the menus options, go to Microsoft Dynamics GP >> Tools >> Routines >> Batch Recovery.

If your batch is listed in the Recovery window, select the check box, and click Continue.

Depending on the severity of the transaction error, the batch will do one of the following:

1. Complete the Posting process
2. Return to the Batch Entry window
3. Continue to give error messages

1. Complete Posting

In some cases, when there aren’t errors in the transactions, the batch will complete its posting. You will be prompted to print the posting report, and everything will finish processing. This is uncommon however, and most recovered batches will require additional steps to correct.

2. Return to Batch Entry

If Batch Recovery is unable to complete the posting, it will mark the batch available for posting and send it back to the Batch Entry window. You will not receive any sort of notification or report that this happened, the batch will simply disappear from the Recovery window.

Go to the Batch Entry window and select or enter your batch. Before attempting to post again, click Print and print an Edit List to Screen. The report printout will tell you what errors are causing the batch to crash so you can address them.

Print the report to screen.

Address any errors noted in the report and post the batch again.

3. Continued Error Message

In other cases, Batch Recovery is unable to post the batch nor send it back to the entry window. This will cause a similar error message, if not the same, as the one received when the batch originally crashed. When this happens, the batch will remain in the Recovery window and will require manually releasing the batch on the SQL Server.

Method 2 – Solve Stuck Batches With SQL

If Batch Recovery does not work, the batch will need to be released via SQL. If you have someone in-house who is comfortable running SQL scripts, below are instructions. Otherwise contact us at Dynamics Business Solutions and we will be glad to help.

Launch SQL Server Management Studio and open a new query against the Company Database. Backup the Batch Master table before making any changes. The script used to back up a single table is:

SELECT * INTO SY00500_BU from SY00500

After the table has been backed up, run the following to find the batch record, replacing XXX with your batch number:

SELECT * FROM SY00500 WHERE BACHNUMB = 'XXX'

To release the batch from its status, execute the following:

UPDATE SY00500 SET BCHSTTUS = 0, MKDTOPST = 0 WHERE BACHNUMB = 'XXX'

Go back into GP and your batch will now be available to print an edit list and / or post.

Sales batches can have additional records that need to be released. You may still receive the following error message when attempting to open:

• “Your previous transaction-level posting session has not finished processing.”

If so, head back over to SQL and run the following script to remove all inactive users. This script will also remove any stranded batch records and is safe to run while users are logged into GP. It will not affect active users.

DELETE FROM DYNAMICS..ACTIVITY

where USERID not in (select loginame from master..sysprocesses)

DELETE FROM DYNAMICS..SY00800

where USERID not in (select USERID from DYNAMICS..ACTIVITY)

DELETE FROM DYNAMICS..SY00801

where USERID not in (select USERID from DYNAMICS..ACTIVITY)

DELETE FROM tempdb..DEX_LOCK

where session_id not in (select SQLSESID from DYNAMICS..ACTIVITY)

DELETE FROM tempdb..DEX_SESSION

where session_id not in (select SQLSESID from DYNAMICS..ACTIVITY)

Next, look for the problem records in the SY00500 Batch Master table by running the below statements against the company database, replacing ‘XXX’ with the User ID of the user receiving the error:

SELECT * from SY00500 where BACHNUMB = ''

SELECT * from SY00500 where BACHNUMB = 'xxx'

If any records are returned, run the delete scripts below:

DELETE SY00500 where BACHNUMB = ''

DELETE SY00500 where BACHNUMB ='xxx'

In GP, verify that you can select existing documents in the Sales Transaction Entry window. To open this window, go to Transactions>> Sales>> Sales Transaction Entry. If you want to delete an existing document, select Delete. If you want to void an existing document, select Void.

Message When Opening Sales Transaction Entry Window

If you continue to receive the error message, or if you cannot select the existing documents in the Sales Transaction Entry window, run the following scripts in SQL to locate problem records in the SOP10100 table, again replacing XXX with the User ID receiving the error.

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 = ' ')

If any records are returned, delete them by running the following:

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 = ' ')

DELETE SOP10100 where BACHNUMB = '' DELETE SOP10100 where BACHNUMB = 'XXX'

Finally, in GP, run Check Links against the Sales Work file. Go to Maintenance >> Check Links, select the Sales Series and insert Sales Work. Click OK and print the report to screen. This will clean up any remaining stranded records in the SQL tables by removing them. If the transactions did not post in the Receivables Management and Inventory modules, they will need to be re-entered in the Sales Transaction Entry window.

Further advance troubleshooting can be done with the following scripts:

1. All Receivables/Payables Scripts. (You can contact Dynamics Business Solutions 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 that will search for any record in the company database and show you the tables the record is in.

Working in SQL can be intimidating and is not recommended unless you have experience working with relational databases and writing queries. Remember to always backup the database before making any changes.

If you need or want any help, contact us at Dynamics Business Solutions and we will be happy to assist you.