How to check for inconsistencies in the Sage 500 ERP surrogate key values
If you are familiar with the database schema of Sage 500 ERP you have likely come across the need to understand the use of surrogate keys in the database. Most tables within 500 utilize surrogate keys as the primary key with a few exceptions – tsmCompany and tsmCountry are two examples that do not. The surrogate key provides a unique index in each table. It should be noted that the surrogate keys are not assigned utilizing the Is Identity column property available for tables in your SQL Server database. Instead, Sage 500 has a specific process to manage the surrogate key assignment. This article will provide a brief overview of this and then will provide a method for checking for inconsistencies in the surrogate key values that could lead to errors when running the Sage 500 application. While these errors should not be common, they do occur and often are related to custom integrations or custom development that did not properly manage the surrogate key values within Sage 500.