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

Symptoms

After a Microsoft Dynamics GP® upgrade, you receive the following error message when launching GP, or upgrading the Collections Management Tables:

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

Additionally, no Collector is available to be selected when opening the Collectors Window (Microsoft Dynamics GP > Tools > Setup > Sales > Collectors).

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 Collectors, the CN01400 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 Dynamics(system) 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 Dynamics database. It will create a backup of the Collector MSTR table:

Select * into CN01400_BACKUP from CN01400

Step II 

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

—> select the Collections – Collector Master table—> 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 Dynamics database and see all the columns. No data should currently be showing:

select * from CN01400

Step III 

In SQL Management Studio, complete the following steps:

Step III.1

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

and

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

CN01400 “Insert To”Script as

CN01400_Backup “Select To” Script As

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

INSERT INTO [dbo].[CN01400]

           ([CollectorID]

           ,[CollectorName]

           ,[Position_Name]

           ,[CN_Email_Address]

           ,[CN_Collector_Phone1]

           ,[CN_Collector_Phone2]

           ,[CN_Collector_Fax]

           ,[CN_User_Defined1]

           ,[CN_User_Defined2]

           ,[CollectionsUser]

           ,[USERID]

           ,[PASSWORD]

           ,[eCollectUser]

           ,[Open_CollectPro_Main]

           ,[Open_CollectPro_Query]

           ,[Open_CollectPro_To_Do]

           ,[CN_Default_Action_Assign]

           ,[NOTEINDX]

           ,[CREATDDT]

           ,[MODIFDT]

           ,[LSTUSRED]

           ,[CN_Signature_ID]

           ,[Refresh_Collections_Main]

           ,[CN_Process_Plans])

       SELECT [CollectorID]

                ,[CollectorName]

                ,[Position_Name]

                ,[CN_Email_Address]

                ,[CN_Collector_Phone1]

                ,[CN_Collector_Phone2]

                ,[CN_Collector_Fax]

                ,[CN_User_Defined1]

                ,[CN_User_Defined2]

                ,[CollectionsUser]

                ,[USERID]

                ,[PASSWORD]

                ,[eCollectUser]

                ,[Open_CollectPro_Main]

                ,[Open_CollectPro_Query]

                ,[Open_CollectPro_To_Do]

                ,[CN_Default_Action_Assign]

                ,[NOTEINDX]

                ,[CREATDDT]

                ,[MODIFDT]

                ,[LSTUSRED]

                ,[CN_Signature_ID]

                ,[Refresh_Collections_Main]

                ,0

         FROM [dbo].[CN01400_BACKUP]