Infor CRM 8.3 Upgrade – Issues with Proper Schema Creation in the Database

We recently ran into an issue when upgrading a customer to 8.3 that I wanted to discuss as it is a potentially big issue that could prevent an upgrade from working correctly, despite no warnings or error messages to alert you.

One of the first steps to upgrading is to install the 8.3 VFS project and to check the “Rebuild schema from project” when you do the restore. This checkbox adds a step which uses the entity model to “back fill” any missing database components. For instance the 8.3 project backup adds a bunch of new fields to a lot of tables (See the Upgrading to Infor CRM v8.3.pdf, starting on page 61). In the upgrade, these new database elements are simply added as properties to the entity model. By checking the Build Schema checkbox what you are really telling the system is to use the entity model as the master schema definition, and generate any missing components into the database that exist in the entity model but not the database.

This rebuild schema option is also available under the Tools menu in the Application Architect.

When running the Rebuild Schema step a message box will show saying “Gathering Managed Entities”. Also, in the Output window you will get details listed of what is is doing.

While there were no errors or warnings logged, there was an “info” message that revealed a problem:

INFO  - ALTER TABLE ALTER COLUMN CITY failed because one or more objects access this column. : The object ‘V_Opportunities’ is dependent on column ‘CITY’. : The object ‘AKS_Opportunities’ is dependent on column ‘CITY’.

The customer’s database we were upgrading had SQL views with Schema Binding enabled. Normally the schema update process is the following for tables:
INFO – Inspecting schema for Address
INFO – Checking Address for Schema changes
INFO – Removing indexes for CITY
INFO – Removing indexes for STATE
INFO – Recreating Indexes for ADDRESS
INFO – Schema changes applied for Address

Because the SQL error was happening, it was not ever reaching the point where it altered the Address table. Therefore ADDRESS5, ADDRESS6, COUNTRYCODE and ENTITYTYPE were never added.

By looking at the output window I found similar messages for other tables as well. The end result was that the new database schema elements that were supposed to be created in some tables were not.

Hopefully Infor will address this and actually report this kind of stuff as errors during this rebuild schema routine so that errors are properly reported as such and occur when restoring the project with that checkbox on or when using the menu item.

While this particular client had a problem with address fields being created, it could potentially be an issue with any of the tables in the system. Without an error being reported it is very likely people will have no idea a problem occurred or that their DB was not properly upgraded until later.

So read those output window messages. Even output that doesn’t show as an error or warning can actually be telling you of a problem.

ABOUT THE AUTHOR

Kris Halsrud

Kris Halsrud is a Senior Analyst / Developer for Customer FX Corporation.

3 Comments

  1. Simple question, maybe not simple answer: Did you find a workaround so that it could apply the schema changes to the database even if it could not successfully remove the index?

    Reply
    • I had to temporarily drop the data bound views and then re-add them after the update for this issue.

  2. Hi Kris,

    While i try to restore the project back up of 8.3 by checking the option, i am facing the same issue. I am getting an error like “object reference not set to …. ” in managed entities wizard.

    But when i ignore the messages (*which i have option to ignore in the error messages prompt) and do the complete rebuild i am able to rebuild the system with out any errors.

    I did a rebuild and redeploy. But i am able to login in to web client. The login page appears when i give correct admin name and pwd it says missing column “version” ,… etc

    Could you please suggest….

    Reply

Submit a Comment

Your email address will not be published. Required fields are marked *

Subscribe To Our Newsletter

Join our mailing list to receive the latest Infor CRM (Saleslogix) news and product updates!

You have Successfully Subscribed!