A get change operation on table CN_Customer_Info failed accessing SQL Data

Symptoms

During a Collections Management installation or upgrade, you receive the following error message:

A get change operation on table CN_Customer_Info failed accessing SQL Data

Reason(s)

The following are the possible reasons why this error populated:

1. The Collections Management installation or upgrade failed on the temporary table CN_Upgrade.
2. A SQL Maintenance needs to be performed on the table related to the Customer Information, the CN00500 table.  

Resolution

Please do not proceed if you do not have any experience in the SQL language. Also do not proceed until you have made a Backup of your Microsoft Dynamics GP Database, your Company Database and saved a copy of your Microsoft Dynamics GP Folder for safe keeping.

Method 1

Step I

Run the following select statement on your affected company database to retrieve this temporary table CN_Upgrade:

Select * from CN_UPGRADE

Step II

If this SQL statement above, generates any data currently available in the table ( CN_Upgrade), please run the following delete Statement:

Delete CN_UPGRADE

Step III

Run the Collections Management Installation Steps:

While other users are logged out of Microsoft Dynamics GP, Launch GP as ‘sa’, and then go to the Sales pane —> Utilities —> Collection Installation and Registration—> Click on  Create/ Upgrade Tables —>  Ensure that your Valid Registration Keys and Expiration Date have been entered—>  then Click on Install Stored Procedures

Method 2

Step I

In SQL Management Studio, run the following query against your Company database. It will create a backup of the current collections_customer info table:

Select * into CN00500_BACKUP from CN00500

Step II 

Open Microsoft Dynamics GP as ‘sa’ —> go to Maintenance —> Click on SQL —> choose the company database where the issue is occurring—> Select Product: Collections Management

—> select the Collections – Customer Info—> and then check all the boxes to the right (recompile, update statistics, drop table, create table, drop auto procedure, create auto procedure) and click Process:

You will know this process completed successfully if you can run the following script against the company database and see all the columns. No data should currently be showing:

select * from CN00500

Step III 

In SQL Management Studio, complete the following steps:

Step III.1

Script the CN00500 table as an “Insert To” as shown below 

and

the CN00500_ BACKUP as a  “Select To” as shown below 

CN00500 “Insert To”Script as

CN00500_Backup “Select To” Script As

Finally, create the SQL Script to include both the Insert statement from the CN00500 and the Select statement from the CN00500_Backup, ensuring that the content of the “insert into” columns matches the number of columns of “the select to” columns.

The generated SQL script will be similar to this SQL script shown below.

Once generated, please run your created SQL Statement in the company database selected in this Step II above, where you have experienced this issue.

INSERT INTO [dbo].[CN00500]

           ([CUSTNMBR]

           ,[CRDTMGR]

           ,[PreferredContactMethod]

           ,[NOMAIL]

           ,[ADRSCODE]

           ,[Time_Zone]

           ,[CN_Credit_Control_Cycle]

           ,[USRTAB01]

           ,[USRTAB09]

           ,[USERDEF1]

           ,[USERDEF2]

           ,[USRDAT01]

           ,[User_Defined_CB1]

           ,[User_Defined_CB2])

SELECT [CUSTNMBR]

  ,[CRDTMGR]

  ,[PreferredContactMethod]

  ,[NOMAIL]

  ,[ADRSCODE]

  ,[Time_Zone]

  ,[CN_Credit_Control_Cycle]

  ,[USRTAB01]

  ,[USRTAB09]

  ,[USERDEF1]

  ,[USERDEF2]

  ,[USRDAT01]

  ,[User_Defined_CB1]

  ,[User_Defined_CB2]

  FROM [dbo].[CN00500_BACKUP]

GO