GP & Lowercase Database IDs

/, Troubleshooting/GP & Lowercase Database IDs

GP & Lowercase Database IDs

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.

Side note: Postmaster Enterprise is a great product… this client uses it heavily and it works like a champ. Check it out if there are any hands-off processes you want to be completed automatically!

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, exact 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 you have 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 actually looked in Dynamics GP myself, as it seemed fairly straight forward 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 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 you what the problems are (not always, but most times this is true). Here’s what the Edit List showed:

AP Distribution edit list messages

“A distribution type is invalid for a destination company distribution”. 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… Intercompany is greyed out and definitely not ticked. 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:

AP Distribution lowercase companyID

Everything looked ok – 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”, 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!

AP Distribution empty error message

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 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 you are logged into.
  • SOP batches were fine because there is 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 you refresh from production to a test environment, 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 you 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 are no errors, and post the batch. Sure enough, that one simple change worked.

The Lesson Learned

If you are refreshing a test company, the value you type in the To Database field MUST be the uppercase value that it was prior to restoring… no getting lazy and typing in a lowercase database ID and assuming it’s all good because your server is case insensitive collation! I suppose if you are not sure, you could look in the DYNAMICS SY01500 table to view the INTERID column for the company you are restoring, to make absolutely sure!

To fix this particular one, the client will end up restoring the db again, with the uppercase name, and that should resolve all of the issues!

 

By | 2017-08-13T11:46:28+00:00 December 12th, 2014|How To & Tips, Troubleshooting|0 Comments

Leave A Comment