Dynamics GP - Customer combiner/modifier and table not found errors
The Customer Combiner and Modifier is a tool that allow a customer to be merged into another customer, including account balances and historical records. The source primary records are then deleted leaving one customer. The modifier just allows the "renaming" of a customer id. For the rest of this post I will discuss the combiner, but the principles apply to the modifier too.
Missing table or Duplicate Key error when combining customers
If the database has tables in a database schema other than the default dbo, then the SQL script behind this tool can come unstuck. The script dynamically builds the update statements to update the contents of the CUSTNMBR field, no matter what table it resides in by iterating through all the tables in the database updating the values in the CUSTNMBR field, if a CUSTNMBR field exists in that table. It also then deletes any entries for the old customer number in a similar manner.
The script correctly finds all the tables that this field occurs in, but does not take into account the schema the table is in. Hence when it comes to apply the update, the update SQL statement assumes the tables all lie in the dbo schema, that for a normal GP install would be true.
If for one reason or another, there are tables in the company database that reside in another schema and those tables contain the CUSTNMR field then this causes an error. This is because the update statement that is dynamically build does not include the schema name, thus when ran in against the default schema, SQL correctly complains it can’t find the table as the schema was not specified.
Invalid object name error when combining customers is shown below:
Getting it to work
I would recommend heavily testing the data modifier tooling on a test instance before running against live data, even, then backup before doing anything. This is very unsupported and you must feel confident about what you are about to do…
The “fix” is easy, just a quick amend to the SQL stored procedure that drives the GP feature. The procedure is named: rmCustomerCombinerMaster
In that script there is a cursor built with the dynamics SQL that is to be executed. Make the change shown in the comments of the TSQL below and update the stored procedure, keeping a copy of the original. The change appends the schema name to each table to allow it to be correctly addressed. Remember that due to the DMO query the combiner can only be ran while logged in to GP with admin rights. You will need to scan down the script to find the DELETE section that does a similar cursor loop for removing the old now renamed customer numbers for tables where it is the primary key.
DECLARE ta_CCCursor INSENSITIVE CURSOR
FOR
--Original line 365
--SELECT 'update [' + o.NAME + '] set CUSTNMBR=' + rtrim(@cEndCustomer) + ' where CUSTNMBR=' + rtrim(@cStartCustomer)
--Replacement, schema aware version
SELECT 'update [' + OBJECT_SCHEMA_NAME(o.id)+'].['+o.NAME + '] set CUSTNMBR=' + rtrim(@cEndCustomer) + ' where CUSTNMBR=' + rtrim(@cStartCustomer)
FROM sysobjects o
,syscolumns c
WHERE o.id = c.id
AND o.type = 'U'
AND c.NAME = 'CUSTNMBR'
AND o.NAME <> 'RM00101'
AND o.NAME <> 'RM00102'
AND o.NAME <> 'RM00103'
AND o.NAME <> 'RM00106'
AND o.NAME <> 'RM00104'
AND o.NAME <> 'ASI82615'
AND o.NAME <> 'CN00500'
AND o.NAME <> 'SVC00950'
AND o.NAME <> 'SVC00960'
AND o.NAME <> 'MDS00501'
AND o.NAME <> 'ASI82610'
AND o.NAME <> 'ASILOC50'
AND o.NAME <> 'ASI82650'
AND o.NAME <> 'CN00400'
AND o.NAME <> 'CN100100'
AND o.NAME <> 'CN100200'
AND o.NAME <> 'PA00010'
AND o.NAME <> 'PA00001'
AND o.NAME <> 'SOP10100'
AND o.NAME <> 'SOP60300'
AND o.NAME <> 'PA00501'
AND o.NAME <> 'PA00511'
AND o.NAME <> 'PA00521'
AND o.NAME <> 'PA00531'
AND o.NAME <> 'PA00532'
AND o.NAME <> 'PA50100'
AND o.NAME <> 'VAT00300'
AND o.NAME <> 'VAT10101'
AND o.NAME <> 'ME147214'
AND o.NAME <> 'RM30701'
AND o.NAME <> 'RM30702'
AND o.NAME <> 'SV00100'
AND o.NAME <> 'PA02301'
AND o.NAME <> 'SOP30200'
AND o.NAME <> 'SOP60200'
AND o.NAME <> 'tcsSOPTB00007_Cust_Add'
AND o.NAME <> 'gpItmCus'
AND o.NAME <> 'SC020330'
ORDER BY o.NAME
DECLARE ta_CCCursor INSENSITIVE CURSOR
FOR
SELECT 'delete [' + OBJECT_SCHEMA_NAME(o.id) + '].[' + o.NAME + '] where CUSTNMBR='
+ rtrim(@cStartCustomer)
FROM sysobjects o
,syscolumns c
WHERE( o.id = c.id
AND o.type = 'U'
Violation of primary key constraint combining customers in Dynamics GP
Sometimes if 3rd party products are in use or customer tables (in any schema) have been introduced to GP, and those tables contain customer number fields as primary keys, this will cause issues for the customer combiner. Luckily there are some stored procedures made available to deal with this issue.
rmCustomerCombinerPre rmCustomerCombinerPost rmCustomerModifierPre rmCustomerModifierPost
Just as with eConnect stored procedures, the customer combiner/modifier provides SQL procedures pre and post combining the customer numbers that can be used as “event handlers”. If you have any custom tables in the database that have a customer number field CUSTNMBR (or one of the other common names for it) and it is a primary key for that table, then you must write your own script to update these tables. If you don’t then a primary key violation error will occur when the GP combiner tries to update the primary key field value from the old customer number to the new customer number, as the new customer number (may) already exist.
Typically I create a duplicate procedure that I call from the pre procedure, this protects it from accidental deletions during upgrades, as these pre and post procedures normally are dropped and recreated then. After upgrade the call to the alternative procedure needs reinstating, but the logic is protected and not lost.
Update the rmCustomerCombinerPre to call your own version, in that version do what you need to make the combiner work and avoid that key violation. In this example deleting the original customer number in the pre means that when the combiner comes to try and update any old vales to the new values in that primary key column, the old value has already been removed in the pre stored procedure, so no exception occurs anymore. There may be other more sophisticated logic that you need for your own implementations, but I leave that up to the reader to figure out.
ALTER PROCEDURE [dbo].[rmCustomerCombinerPre] @I_charStartCustomer CHAR(30) OUTPUT
,@I_charEndCustomer CHAR(30) OUTPUT
,@cStartCustomer CHAR(50) OUTPUT
,@cEndCustomer CHAR(50) OUTPUT
,@O_iErrorState INT OUTPUT
AS
SELECT @O_iErrorState = 0
EXEC myschema.rmCustomerCombinerPre @I_charStartCustomer OUTPUT ,@I_charEndCustomer OUTPUT,@cStartCustomer OUTPUT,@cEndCustomer OUTPUT,@O_iErrorState OUTPUT
RETURN
-- =============================================
-- Author: Tim Wappat
-- Create date: 2017-03-09
-- Description: Pre Customer Combiner
-- Ran by the GP customer combiner before it combines customers
-- deals with tables
-- =============================================
ALTER PROCEDURE myschema.rmCustomerCombinerPre
@I_charStartCustomer CHAR(30) OUTPUT
,@I_charEndCustomer CHAR(30) OUTPUT
,@cStartCustomer CHAR(50) OUTPUT
,@cEndCustomer CHAR(50) OUTPUT
,@O_iErrorState INT OUTPUT
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Remove records that will create key violation
DELETE myschema.INVOICE_CUSTOMER WHERE CUSTNMBR= @I_charStartCustomer
SELECT @O_iErrorState = 0
END
GO
GRANT EXECUTE ON myschema.rmCustomerCombinerPre TO DYNGRP
My other post also goes into some more detail on naming your objects in the database and looks at this as an example of why developers should think carefully about field and object names.
Update: 2016-08-16
Post in community forum where this time it was trigger causing an issue.