What is this error message about SY00100 ??

Recently I was working on an upgrade project for a customer going from an old GP 2010 to the most recent build of 2018R2 (18.00.0704). The upgrade path involved hops thru 2013 & 2015 before I got to the final build. The 2 hops went without major issues except a know bug about SmartList tables that has been plaguing GP Utilities since at least 2015 (see more about this in my other blog post).

Once the GP instance was fully upgraded to 2018R2, I installed GP Power Tools (GPPT) to perform a database validation, as the instance had moved across 2 different SQL servers, I was obviously missing the GP user’s security in SQL, as I don’t bother to re-instate them between the hops.

Soon as I started GPPT DB validation, within a few seconds (and table scans) it popped up an error message :

I don’t remember having ever come across this error message before in my past upgrades, so this was kind of mind-bogging. It wouldn’t prevent GPPT to go on once you hit the OK button, and the Database Validation was actually working just fine, but it was bugging my brain.

SY00100 is the SYSTEMDB reference table for each company DB. Jen Kunz and Steve Endow blogged about that table in the past, and I wont go into the details, but needless to say that everything was looking fine in those tables.. As I compared the SY01500 table holding all the companies with the SY00100 in each company, the references was looking good to me.. or so I thought.

I contacted @winthropdc support to ask David about this message and he looked into his code.. Oddly enough, he reported that the error message would come up because the process would look for a database called [DYNAMICS], which it couldn’t locate apparently.

It struck me when I started the initial migration process that the DYNAMICS database was actually spelled out in a mixed-case way, probably a mistake from the partner that last restored/implemented GP on this server.

David confirmed that Dexterity would try to compare the System DB name in SQL to the name in the SY00100 table and not consider “Dynamics = DYNAMICS” when doing the compare, whereas SQL wouldn’t care at all if your instance was setup according to Microsoft recommendation of using the Collation SQL_Latin1_General_CP1_CI_AS.

But Dexterity is not following SQL’s collation rules, as it always does a binary comparaison, which is case sensitive and this was causing the problem of the validation.

Once I had renamed the “Dynamics” DB name to “DYNAMICS” I could re-run the DB Validation in GPPT and there would be no error message anymore coming up!

The oddity here is that the initial GP installation process since 2013 allows you to enter a distinctive System DB name for multi-tenant setup, but doesn’t care to check if it was entered all Upper-Case or not (I double checked that and No, the Setup process always writes the system DB name in UPPER case only). Whereas GP Utilities doesn’t let you enter mixed-case company DB names, so they are consistent across the board.

The SY00100 is addressed in several scripts within GP at some points when dealing with configuration and maintenance. It could well be that you would run into this type of message, even without using GPPT. So now you know how to fix the issue. GPPT’s code has been fixed already to handle such situations, so this shouldn’t be a problem anymore in the future.

Be careful when the next time you come across a GP instance where you see either the System DB (DYNAMICS) or any company DB written in anything else than all UPPER case… it could well be a trouble-maker situation.

Hope this helps and until next time,
@GP_Beat

NB: GPPT has already been fixed and build 27.4 addresses the issue with the not all-Upper case system DB name.