A get/ change operation on table 'CN_Notes' failed accessing SQL Data.

Symptoms

After a Microsoft Dynamics GP® upgrade, you receive the following error message when accessing a Collections Management Window such as the Collections Main Window or upgrading the Collections Management Tables:

A get/ change operation on table 'CN_Notes' failed accessing SQL Data.

Reason(s)

The following are the possible reasons why this error populated:

1. Following the Microsoft Dynamics GP® upgrade, Collections Management was not correctly upgraded as “sa”  from the Collections Management Installation and Registration Window
2. A SQL Maintenance needs to be performed on the table related to the Collections Actions, the CN00100 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 creates a backup of the current Collections Notes Master table:

Select * into CN00100_BACKUP from CN00100

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 – Notes—> 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 CN00100

Step III 

In SQL Management Studio, complete the following steps:

Step III.1

Script the CN00100 table as an “Insert to” as shown below 

and

the CN00100_ BACKUP as a “Select to” as shown below 

CN00100 “Insert to”Script as

CN00100_Backup “Select to” Script As

Finally, create the SQL Script to include both the Insert statement from the CN00100 and the Select statement from the CN00100_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 Script in the company database selected in this Step II above, where you have experienced this issue.

INSERT INTO [dbo].[CN00100]

           ([CUSTNMBR]

           ,[CPRCSTNM]

           ,[DATE1]

           ,[Contact_Date]

           ,[TIME1]

           ,[Contact_Time]

           ,[NOTEINDX]

           ,[RevisionNumber]

           ,[CN_Group_Note]

           ,[Caller_ID_String]

           ,[Action_Promised]

           ,[ActionType]

           ,[Action_Date]

           ,[Action_Assigned_To]

           ,[Action_Completed]

           ,[ACTCMDSP]

           ,[Action_Completed_Date]

           ,[Action_Completed_Time]

           ,[Amount_Promised]

           ,[Amount_Received]

           ,[USERID]

           ,[Note_Display_String]

           ,[CNTCPRSN]

           ,[ADRSCODE]

           ,[USERDEF1]

           ,[USERDEF2]

           ,[USRDAT01]

           ,[PRIORT]

           ,[NOTECAT]

           ,[NoteStatus]

           ,[Action_Cancelled_By]

           ,[Action_Cancelled_Date])

       SELECT [CUSTNMBR]

                ,[CPRCSTNM]

                ,[DATE1]

                ,[Contact_Date]

                ,[TIME1]

                ,[Contact_Time]

                ,[NOTEINDX]

                ,[RevisionNumber]

                ,[CN_Group_Note]

                ,[Caller_ID_String]

                ,[Action_Promised]

                ,[ActionType]

                ,[Action_Date]

                ,[Action_Assigned_To]

                ,[Action_Completed]

                ,[ACTCMDSP]

                ,[Action_Completed_Date]

                ,[Action_Completed_Time]

                ,[Amount_Promised]

                ,[Amount_Received]

                ,[USERID]

                ,[Note_Display_String]

                ,[CNTCPRSN]

                ,[ADRSCODE]

                ,[USERDEF1]

                ,[USERDEF2]

                ,[USRDAT01]

                ,[PRIORT]

                ,[NOTECAT]

                ,[NoteStatus]

                ,[Action_Cancelled_By]

                ,[Action_Cancelled_Date]

         FROM [dbo].[CN00100_BACKUP]

GO