Customer combiner 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.

image

Missing table

If the database uses tables in a schema other than dbo, then the SQL script behind this tool can come aground. The script dynamically builds the update statements to update the contents of the CUSTNMBR field, no matter what table it resides in.

The script correctly finds all the tables that this field occurs in, but does not take in 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.

image

Getting it to work

I would recommend heavily testing the data modifier tooling on a test instance before running in live, 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 the tool drives. 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.

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
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.

Pingbacks and trackbacks (1)+

Add comment

Loading