Fixing a Stuck Batch in Dynamics GP
All users of Dynamics GP will eventually come across this issue. One of the most common questions we get “How do I fix a stuck batch?” You’ve built up your batch, and you post it, and then, nothing happens. You do a lookup in your batch window, and you see your batch, but you see it’s status says one of:
-
Posting
-
Receiving
-
Busy
-
Marked
-
Locked
-
Edited
Going to the Batch Recovery won’t work. Either you won’t see your batch there, or the recovery process doesn’t work. You’re out of ideas. Now what?
Microsoft has recognized this issue. The official reason for this is that there may have been a power surge or network disruption.
EXPERT TIP: If this happens frequently, we suggest two options for you.
(1) Consider setting up a “Process Server.” A processing server handles resource-intensive operations such as posting calculations or report calculations in large batches instead of your local computer. If other users suffer when you print or post a large batch, this might be the answer for you.
(2) Also, you might consider moving your Dynamics GP online. There are excellent solutions for this, including GP365.ca. From there, you shouldn’t have any more “network disruption” or “power surge” related GP issues. In fact, you’ll find GP runs blazingly fast from then on.
We will now walk you through the condensed version of what works “most” of the time to fix the issue. Before we get started you will need administrative access to the SQL server and some knowledge of SQL statements. All right, so now we understand something external to GP disrupted the posting process. But what do we do now?
Step 1: Take a backup of the company database in SQL, just in case – it will be easy to restore! We aren’t doing anything risky, but, you never know!
Step 2: Have everyone log out!
Step 3: Login to SQL Management Studio and delete the following tables. Generally speaking, what these SQL statements are doing is making sure that if GP thinks someone is in any batch, then that information should be removed.
DELETE DYNAMICS..ACTIVITY
DELETE DYNAMICS..SY00800
DELETE DYNAMICS..SY00801
DELETE TEMPDB..DEX_LOCK
DELETE TEMPDB..DEX_SESSION
In my experience, this step works 20% of the time.
Step 4: Go to the SQL table called Posting Definitions Master (SY00500). This table lists all the batches in the company, regardless of module, and some basic information around them. The SQL statement “resets” the batch in two ways:
-
It makes sure the batch is not “marked to post.” Think of the series post or master post window in GP, where you can checkmark the batch to post. That’s what this statement unchecks.
-
Also, it sets the status of the batch back to “Available.” It may be marked to a different status, such as one related to an error message, or where GP incorrectly thought it was in the process of posting.
UPDATE SY00500 SET MKDTOPST=0, BCHSTTUS=0 where BACHNUMB=’XXX’
This is the solution the vast majority of the time. I would estimate this is the appropriate fix 75% of the time.
BUT WAIT! Do you want your batch go back to “Available”? That’s the very first step of a batch, where you are just setting it up. The answer is not always Yes.
There is a specific situation where you need to set your batch to a different status. The situation is whenever you are working with an AP payment batch.
Think about payment batches. First, you set it up. That’s one status. Then you print cheques (or take some steps towards processing EFT payments). Finally, you post the batch. There are three statuses we went through. If you have already printed cheques (or processed EFT remittances… etc), then you do NOT want to reset the batch back to “Available.” You want to set it to “Cheques Printed,” or maybe “Remittances Processed.”
To summarize, before running that SQL statement on the SY00500 table, ask, “What kind of batch is this? Is this an AP cheque batch? Is it an AP EFT payment batch??”
If it’s neither, you can run the Microsoft statement against the Company SQL database:
UPDATE SY00500 SET MKDTOPST=0, BCHSTTUS=0 where BACHNUMB=’XXX’
If it is an AP Cheque batch, run this statement against the Company SQL database:
UPDATE SY00500 SET MKDTOPST=0, BCHSTTUS=110 where BACHNUMB=’XXX’
If it is an AP EFT Payment batch, run this statement against the Company SQL database:
UPDATE SY00500 SET MKDTOPST=0, BCHSTTUS=130 where BACHNUMB=’XXX’
Microsoft has written a knowledge base document that covers a more detailed process if interested or our steps didn’t work from you. Here it is:
https://support.microsoft.com/en-us/help/850289/a-batch-is-held-in-the-posting-receiving-busy-marked-locked-or-edited
You can also reach out to us at any time by clicking the contact us or chat functions, and we can assist!
Thank you!
-GP365 Team
