Sage 500 ERP Surrogate Keys and Checking for Inconsistency

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.

One Disclaimer before we get started:  It should not be assumed that there is enough detail in this article to provide a full and complete understanding of surrogate keys and their use in Sage 500.  Please work with a Sage 500 ERP certified partner if you need more information about surrogate keys and the dos and don’ts related to them.

Sage 500 ERP Key Objects Related to Surrogate Key Assignment

Within Sage 500, the following objects are important regarding surrogate key assignment.

Table:                    tciSurrogateKey

Purpose:              This table manages the next key that will be assigned for each table in the Sage 500 database that utilizes Surrogate Keys as their primary key.  This table has two fields:

• TableName – Each table that utilizes surrogate keys will have an entry in this table
• NextKey – This is the next key value that will be assigned to the associated table

If you execute the following query in SQL Server Management Studio you can see the values within your Sage 500 database for each table:

SELECT * FROM tciSurrogateKey

You can further narrow this down by looking for a specific 500 table such as tarCustomer:

SELECT * FROM tciSurrogateKey

WHERE TableName = 'tarCustomer'

You should expect to see a result such as:

tored Procedure:             spGetNextSurrogateKey

Purpose:              This stored procedure is utilized by Sage 500 to ensure that there is a consistent method for capturing surrogate keys when needed and for ensuring that the Next Surrogate Key value is updated with each call.

Sage 500 ERP Surrogate Key Inconsistencies and How to Check for Them

An inconsistency arises when the NextKey value in the tciSurrogateKey table is less than the maximum value of the key for its respective table that is managed by the Sage 500 surrogate key process.  If this condition occurs, a user may get an error when attempting to create a new record through the user interface.  For example, they may receive a duplicate key error similar to this:

Some examples of how we have seen this occur:

• Records are inserted directly in a given table without utilizing the procedure that was created for capturing keys. This is common when someone builds a custom data import instead of using the tools provided for importing data into 500.
• A developer creates a program that manipulates data in a standard 500 table and does not utilize the 500 procedures for getting the next surrogate key.
• Data fixes are applied to the database and considerations for the surrogate key are not taken into account.
• Integrations into Sage 500 are developed that do not take into account proper methods for assigning and updating the surrogate key value.

If you run into the above condition, we have created a script that will help identify tables where the surrogate key has become out of sync with the data in the tables.  Please run this script in SQL Server Management Studio against your Sage 500 ERP database.

If you get any results from this script, you will want to review the tables identified to determine if there is an issue.  As noted in the script there are some tables that would not raise concerns.  These include work tables and tables utilized during the posting processes of Sage 500.  We have attempted to exclude these from the script, but we cannot guarantee that we have identified all tables that should be excluded.

If it is determined that you do have an issue with a table, it would be recommended that you work with your Sage 500 ERP Partner or Sage Support to determine how best to resolve the issue.