Some days I feel like I'm on a game show, where troubleshooting is a contest… "I'll take strange GP errors for $400 Alex!".
Today is one of those days!
One of my clients imports in SOP and Payables transactions via eConnect and then uses Postmaster Enterprise to post the batches. Today one of the QA users was doing some testing of GP and their integrations after a recent database refresh from the production company. She calls me and says something isn't working right. Postmaster keeps logging posting errors but the batches look fine and nothing has changed except refreshing the environment.
The Issue
Postmaster kept reporting an issue when posting any new Payables Transaction batches, but the SOP batches were fine and posted through. The error message emailed to them was "Process Timeout".
Okay… pretty vague. But there was also a second emailed error after each attempt to post the batch: "System Error Message Exception" and the text of the email was: "Illegal address for field 'TPEInitialize' in script '[Not Found]'. Script terminated.".
I Googled this error and got a hit on Steve Endow's blog, the same text, and the description of Steve's findings were suspiciously similar to my client's situation: fairly customized, custom tables, etc. so I thought that was great… nothing beats NOT being the first one with an issue so there is somewhere to start.
It sounded like permissions errors. That was entirely reasonable to me as they recently refreshed their environment from production and often permissions need to be re-granted. Simple enough…
Troubleshooting
Up to this point, I hadn't looked in Dynamics GP myself, as it seemed fairly straightforward to troubleshoot remotely. Then the user asked me to come take a look at the batches in GP. The batches were sitting in Batch Recovery so we proceeded to free them up, so we could look at them.
The first thing I asked her to do was open one of the batches and print the Edit List to the screen. If it's not posting through, it's not a Postmaster issue but something in GP itself preventing it from being posted. This should be easy enough to determine as the Edit List normally shows users what the problems are (not always, but most times this is true). Here's what the Edit List showed:
Hmmm. This sounds suspiciously like it thinks there is an intercompany transaction here. The client doesn't use Intercompany (and it's not enabled in the system registration).
We opened up the transaction on screen in Payables Transaction Entry and it looks "normal" to me… The intercompany field is greyed out and not enabled on the transaction. Since it says "Distributions for this transaction contain errors" I knew it wouldn't be visible on the main screen, so we looked at the Distributions on this transaction:
Everything looked okay. The distribution types on this transaction were a PURCH with a PAY (payment recorded at the same time). However, the database ID in "Co. ID" was in lowercase. That looked strange to me but otherwise, the distribution looked fine. This particular database should be called "SYS01" (uppercase SYS), but the fact that it was being displayed in lowercase made me wonder what happened.
Closing the distribution window usually triggers errors and warnings and this did as well, except the message window was blank! How useful!
From here, I wanted to type in a transaction and see what it looked like, instead of looking at a transaction that was imported via eConnect. I typed in a transaction and it looked completely normal, including the uppercase database ID. Odd that an imported one shows lowercase and a manually typed one doesn't. This client's Payables import doesn't import distributions, it's automatic, so they aren't even passing invalid information.
The last step in troubleshooting then was to go to SQL to look at the table. Sure enough, the database ID for this was showing as lowercase. Then the pieces started to fit together and I did some testing to see if my theory was correct.
The Result
My theory was this:
- The eConnect transaction has a lowercase database ID because that is the SQL database ID being used.
- The manually typed-in transaction does not have a lowercase database ID because the SY01500 table has the proper "INTERID" value for the company the user is logged into.
- SOP batches were fine because there was no Intercompany module impact on SOP.
- Payables batches have the option to use Intercompany therefore there is an INTERID value in the tables which is always filled in even for non-Intercompany transactions. Hence, when a test company is refreshed from a production company, one recommended script goes through GP tables like this and updates these references to the logged-in company ID.
- Even though the SQL server is set to a case insensitive collation, clearly something in the GP business logic is comparing the INTERID on the transaction to the INTERID on the company users are logged into and looking for an exact match, case sensitive. I don't know why… but it seems obvious that is what it does.
To test this theory I simply updated the PM10100 table (work distributions) and set the INTERID to "SYS01", then re-printed the Edit List to verify there were no errors, and posted the batch. Sure enough, that one simple change worked.
The Lesson Learned
When refreshing a test company, the value users type in the To Database field MUST be the uppercase value that it was before restoring… no getting lazy and typing in a lowercase database ID and assuming it's all good because the server is case insensitive collation! I suppose if someone is not sure, they could look in the DYNAMICS SY01500 table to view the INTERID column for the company they are restoring, to make sure!
To fix this particular one, the client will end up restoring the database, with the uppercase name, and that should resolve all of the issues!
