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

By Professional Advantage Wednesday, February 17, 2021

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_Promised' failed accessing SQL Data.

Reason(s)

The following are the possible reasons why this error populated:

Following the Microsoft Dynamics GP® upgrade, Collections Management was not correctly upgraded as “sa” from the Collections Management Installation and Registration Window
A SQL Maintenance needs to be performed on the table related to the Collections Actions, the CN00600 table.

Resolution

Method 1

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

SELECT * FROM CN_UPGRADE

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

DELETE CN_UPGRADE

Run the Collections Management Installation Steps:
1. While other users are logged out of Microsoft Dynamics GP, Launch GP as ‘sa’.
2. Go to the Sales pane —> Utilities —> Collection Installation and Registration.
3. Click on Create/ Upgrade Tables.
4. Ensure that your Valid Registration Keys and Expiration Date have been entered.
5. Click on Install Stored Procedures.

If the issue still occurs after updating the workstation and using the steps above, the CN_Query_MSTR table (CN00600) will need to be recreated using SQL Maintenance using the steps in Method 2.

Method 2

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

select * into CN00600_BACKUP from CN00600

Run SQL Maintenance on the Collections – Query Master table.
1. Open Microsoft Dynamics GP as the ‘sa’ user.
2. Open the SQL Maintenance window.
  1. Dynamics GP > Maintenance > SQL
3. Choose the company database where the issue is occurring.
4. Select Collections Management from the Product dropdown menu.
5. Select the Collections – Query Master from the list section of the window.
6. Check all the boxes to the right
  1. Recompile
  2. Update Statistics
  3. Drop Table
  4. Create Table
  5. Drop Auto Procedure
  6. Create Auto Procedure
7. The window should appear as seen in the following screenshot.
8. Click Process:

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

In SQL Management Studio, complete the following steps:
1. Script the CN01400 table as an Insert To as shown below.
2. Script the CN01400_ BACKUP as a Select To as shown below.
3. Finally, create the SQL Script to include both the Insert statement from the CN00600 and the Select statement from the CN00600_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 2 above, where you have experienced this issue.

INSERT INTO [dbo].[CN00600]

([Query_ID]

,[Query_Description]

,[STCUSTID]

,[STCUSTNM]

,[STTCLSID]

,[STTUDEF1]

,[STSPRSID]

,[STSLSTER]

,[StartPrefContactMethod]

,[CNStartTimeZone]

,[STRTUDEF]

,[STUDDAT1]

,[STUDTAB1]

,[ENCUSTID]

,[ENCSTNAM]

,[ENDCLSID]

,[ENUSRDF1]

,[ENSPSNID]

,[ENSLSTER]

,[CNEndTimeZone]

,[EndPrefContactMethod]

,[ENUSRDEF]

,[ENDUDDT1]

,[ENDUDTBL]

,[StartCreditControlCycle]

,[EndCreditControlCyle]

,[CONNAACT]

,[DateSelection]

,[QRYSTCDTPER]

,[QryEndCrLimitPeriod]

,[QRYSTCDTAMT]

,[QryEndCreditLimitAmt]

,[QryStartDateToken]

,[QryEndDateToken]

,[STRTNGDT]

,[ENDINGDT]

,[Starting_Trx_Days]

,[Ending_Trx_Days]

,[Start_Caller_ID]

,[Start_Contact_Date]

,[Start_Action_Assigned_To]

,[Start_Action_Promised]

,[Start_Action_Date]

,[SNOTPRTY]

,[QRYACTCOM]

,[End_Caller_ID]

,[End_Contact_Date]

,[End_Action_Assigned_To]

,[End_Action_Promised]

,[End_Action_Date]

,[ENOTPRTY]

,[QCMFLAG]

,[Start_Credit_Manager]

,[End_Credit_Manager]

,[CUSTCSTS]

,[QueryBalanceReturned]

,[QueryExcludeOlderBalance]

,[QryExcludeifPaymentMade]

,[QueryNumberofDays]

,[QueryNoteExists]

,[STUDTAB2]

,[ENDUDTBL2]

,[USERLANG]

,[CN_Credit_LevelStart]

,[CN_Credit_LevelEnd]

,[Query_Exclude_Partial_Pa]

,[CN_BalanceOverCreditLim]

,[StartNoteStatus]

,[EndNoteStatus]

,[QueryInvoiceDocumentID]

,[Start_Collection_Plan_ID]

,[End_Collection_Plan_ID]

,[CN_Query_Type]

,[TXTFIELD])

SELECT [Query_ID]