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.

Advertisements

2 thoughts on “What is this error message about SY00100 ??

Add yours

    1. Thanks Sheila. If anyone is installing GP by the book, this should never happen in the first place. But sometimes some people get creative when moving around GP instances and they don’t follow the books 🙂

      Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Powered by WordPress.com.

Up ↑

SID-500.COM

Windows . PowerShell . Automation . Network Engineering . Cyber Security

JenKuntz.ca

Dynamics GP & other thoughts!

SergesPoint

Tricks and trips about SharePoint

Salim Adamon - Dynamics CRM Blog

Everything Dynamics CRM from a technical, architectural and business perspective

The Official Blog for Training Dynamo LLC

Microsoft Dynamics GP - Tips, Tricks, News and Thoughts

Dynamics GP Tips and Traps For Users and Developers

Talking about Great Plains, Dexterity, SQL, and some C#

smay772

A fine WordPress.com site

Tom Roush's Blog

Musings on Life, Lessons, and Laughter...

Belinda Allen, Microsoft MVP Business Solutions

Things I've learned from years of working with Microsoft Dynamics GP.

%d bloggers like this: