This is quite a common error message you may get in Dynamics GP when trying to post a GL Batch in Financial. The full message actually starts with “The Stored procedure glpPostBatch returned the following results: DBMS: 0, Microsoft Dynamics GP: 20052”
And that is the problem. The message means nothing and doesn’t give you any hint about what the real problem might be. Most of the time it can be that your JE entry contains a blank line at the end of the transaction lists, as it has been reported in the past by some other users in the community forums. The simple fix would be to jump to the last line and delete the line. But in my case, this wouldn’t work and no matter how we tried to post this batch, it would fail and get into recovery mode.
My first hint was to look into the SY00500 table that holds the batches in the company table, but couldn’t see anything unusual. The next step was to check the related entries in the GL10000 & GL10001 tables, but nothing suspicious either there.
What I did next was to use the GP PowerTools to run a DEXSQL trace file just right before starting to post the batch, and stop it right when the error would come up. What I found was quite strange to me as I had never seen such an error message in my entire GP life :
/* Date: 03/24/2020 Time: 10:46:37 SQLSTATE:(01000) Native Err:(3606) stmt(256926000):*/ [Microsoft][SQL Server Native Client 11.0][SQL Server]Arithmetic overflow occurred.*/
This came up right after about 20 times the same command was called to execute the glpPostBatch, which seemed to be some timeout limit, though I can’t confirm that for sure. Next to the statement above came the lines that would throw the error message on screen and then put the batch into recovery mode. So much for the DEXSQL trace, but what could actually cause GP to run into an Arithmetic overflow??
Since the JE entry was quite lengthy and composed by over 350 lines, it took some time to scroll thru all the Debit & Credit values to check for unusual numbers. As my query was simply a SELECT * from GL10001 WHERE BACHNUMB like ‘BatchID’, I didn’t saw the problem right upfront. But after sorting the output by line sequence number, the 1st line caught my eye immediatly:
This was supposed to be a Journal Entry for the Canadian Payroll (hint: CPY) , but unlike the usual batches that are normally generated from the Payroll module itself, this one was created manually by the user for some odd reasons (irrelevant to this case). The way the JE was created is actually much more relevant. The user took an existing previous posted JE from the payroll, knowing that the numbers of entries and amounts where pretty similar, exported the result from the GP report into a CSV file and opened that in Excel. The output would look like this :
The blank column ‘A’ was actually inserted by the user, as it was instructed in the past (long time ago) when using the Excel copy / paste feature to create a JE. The user would then select Column A to F, without the headers of course, and paste the data in GP.
Now for those who are familiar with the Excel Copy / Paste feature, this isn’t quite the format that should be used, and for some reason this was causing havoc in the GL10001 table when the entries were created. I couldn’t figure out from where the strange Exchange Rate value came, but once the line was deleted and re-added to the JE, it would post to GP without any issues. The sheer impossible high value for the Exchange rate would cause SQL to actually error out with the Arithmetic Overflow message, as this was probably way too big for the regular currency field used in the tables. On top of that, the columns in the wrong sequence above would cause the entries in the JE to be incorrect, as it would not paste the Debit & Credit values correctly, but instead take the Date column, and it would end up in GP showing complete incorrect values, which had all to fixed manually.
I explained to the user that the format that should be used to copy / paste from Excel was actually very simple and that proved to work like a charm. Note that the 1st column with the Distribution Reference data should be limited to 30 characters, as otherwise it would just be cut off in GP.
Jen Kuntz has published an excellent blog post back in 2017 about how to use the Excel Copy / Paste feature for GL Journal Entries.
Hope that you enjoyed reading this and until next post, wish you a happy life.