How to Fix Accountmate Error When Saving a Change in Customer Maintenance.

Updating a customer record causes an error Cannot save. Customer Maintenenace – Connectivitiy error: [Microsoft][ODBC SQL Serve Driver]]SQL Server] String or Binary data would be truncated. All your changes have been reverted.

Scenario: The customer maintenance has a customized field Temp Credit Increase, Valid Until date field and vanilla field credit limit. The user updates the Valid Until to a new date 11/30/2023.

Accountmate Customer Maintenance Error

The user tries to save the record but gets this error.

Accountmate Customer Maintenance Error Message

After investigating, we found the FoxPro program passes a SQL statement to SQL Server to update a table causes this error. So, running the SQL profiler, we get the SQL query statement (Sample at end) that updates the customer table. Then we run the query in the SQL database, we get the error below on the SQL side.

Accountmate SQL error

There are a few possible solutions to correct this problem. (This requires advance knowledge of SQL programming or Foxpro language.)

Solutions:

We spoke to the client about the data that was being stored to the field mcrhistory. They said they are not using the data stored on that field. Therefore, created a stored procedure to check the field to see if the field mcrhistory length size is near 8000 characters to delete the field in sql agent job.
Another step is to comment out the code in the Accountmate arcust form to prevent the update to the mcrhistory field on the form.

NOTE: We manually run the query in the SQL database below without the additional conditions in the where clause and just this portion on the where clause. It does not have an issue updating the field mcrhistory.

WHERE ccustno=’TESTCUST01′

Sample query statement:

UPDATE arcust SET tmodified=’20231126 11:58:21.000′,ntempcrinc=15000.0000,mcrhistory=’Temp Inc: $15000.00 Create Date: 11/26/2023 11:58:21 AM Valid Until: 11/30/2023

Temp Inc: $100000.00 Create Date: 09/28/2023 02:17:47 PM Valid Until: 10/31/2023

Temp Inc: $100000.00 Create Date: 07/17/2023 10:26:10 AM Valid Until: 08/31/2023

Temp Inc: $100000.00 Create Date: 06/05/2023 04:20:47 PM Valid Until: 06/30/2023

Temp Inc: $100000.00 Create Date: 04/12/2023 10:11:17 AM Valid Until: 05/31/2023

Temp Inc: $100000.00 Create Date: 03/02/2023 10:32:09 AM Valid Until: 03/31/2023

Temp Inc: $80000.00 Create Date: 01/04/2023 10:51:06 AM Valid Until: 02/28/2023

Temp Inc: $80000.00 Create Date: 11/03/2022 11:08:27 AM Valid Until: 12/31/2022

Temp Inc: $80000.00 Create Date: 09/08/2022 02:53:55 PM Valid Until: 10/31/2022

Temp Inc: $150000.00 Create Date: 08/19/2022 02:49:05 PM Valid Until: 08/31/2022

Temp Inc: $150000.00 Create Date: 06/13/2022 11:45:32 AM Valid Until: 07/31/2022

Temp Inc: $150000.00 Create Date: 04/06/2022 10:33:25 AM Valid Until: 05/31/2022

Temp Inc: $150000.00 Create Date: 01/07/2022 08:25:08 AM Valid Until: 03/31/2022

Temp Inc: $150000.00 Create Date: 12/14/2021 02:37:03 PM Valid Until: 12/31/2021

Temp Inc: $100000.00 Create Date: 11/08/2021 11:22:53 AM Valid Until: 12/31/2021

Temp Inc: $100000.00 Create Date: 10/05/2021 11:34:29 AM Valid Until: 10/30/2021

Temp Inc: $100000.00 Create Date: 09/01/2021 12:21:01 PM Valid Until: 09/30/2021

Cred Limit: $50000.00 Eff. Date: 09/01/2021 12:20:39 PM

Temp Inc: $300000.00 Create Date: 07/19/2021 12:46:10 PM Valid Until: 08/31/2021

Temp Inc: $300000.00 Create Date: 06/02/2021 09:35:53 AM Valid Until: 07/15/2021

Temp Inc: $300000.00 Create Date: 04/21/2021 02:21:10 PM Valid Until: 05/31/2021

Temp Inc: $300000.00 Create Date: 03/04/2021 10:52:25 AM Valid Until: 04/15/2021

Temp Inc: $300000.00 Create Date: 02/04/2021 11:01:07 AM Valid Until: 02/28/2021

Temp Inc: $300000.00 Create Date: 01/05/2021 04:57:46 PM Valid Until: 01/31/2021

Temp Inc: $300000.00 Create Date: 12/01/2020 02:20:44 PM Valid Until: 12/31/2020

Temp Inc: $300000.00 Create Date: 11/02/2020 01:33:20 PM Valid Until: 11/30/2020

Temp Inc: $150000.00 Create Date: 10/23/2020 11:45:14 AM Valid Until: 10/31/2020

Temp Inc: $200000.00 Create Date: 09/14/2020 10:57:08 AM Valid Until: 10/15/2020

Temp Inc: $200000.00 Create Date: 08/12/2020 12:29:31 PM Valid Until: 08/31/2020

Temp Inc: $150000.00 Create Date: 06/18/2020 03:41:12 PM Valid Until: 07/15/2020

Temp Inc: $150000.00 Create Date: 05/06/2020 11:46:23 AM Valid Until: 06/15/2020

Temp Inc: $150000.00 Create Date: 03/26/2020 03:42:53 PM Valid Until: 04/30/2020

Temp Inc: $150000.00 Create Date: 02/24/2020 11:17:29 AM Valid Until: 03/15/2020

Temp Inc: $100000.00 Create Date: 01/08/2020 03:21:58 PM Valid Until: 02/29/2020

Temp Inc: $100000.00 Create Date: 11/18/2019 12:31:14 PM Valid Until: 12/15/2019

Temp Inc: $100000.00 Create Date: 10/08/2019 11:22:41 AM Valid Until: 11/15/2019

Temp Inc: $100000.00 Create Date: 08/28/2019 11:56:22 AM Valid Until: 09/30/2019

Temp Inc: $50000.00 Create Date: 08/28/2019 11:55:43 AM Valid Until: 09/30/2019

Temp Inc: $50000.00 Create Date: 07/31/2019 12:05:40 PM Valid Until: 08/31/2019

Temp Inc: $50000.00 Create Date: 06/26/2019 12:45:58 PM Valid Until: 07/15/2019

Temp Inc: $150000.00 Create Date: 04/08/2019 11:51:07 AM Valid Until: 05/31/2019

Temp Inc: $150000.00 Create Date: 03/06/2019 11:09:47 AM Valid Until: 03/31/2019

Temp Inc: $150000.00 Create Date: 02/01/2019 05:32:31 PM Valid Until: 02/28/2019

Temp Inc: $150000.00 Create Date: 12/14/2018 04:11:47 PM Valid Until: 01/31/2019

Temp Inc: $150000.00 Create Date: 10/25/2018 11:43:07 AM Valid Until: 11/30/2018

Temp Inc: $100000.00 Create Date: 10/10/2018 10:27:41 AM Valid Until: 11/30/2018

Temp Inc: $65000.00 Create Date: 09/17/2018 05:03:56 PM Valid Until: 10/15/2018

Temp Inc: $65000.00 Create Date: 07/27/2018 12:40:00 PM Valid Until: 08/31/2018

Temp Inc: $45000.00 Create Date: 07/27/2018 12:39:25 PM Valid Until: 08/31/2018

Temp Inc: $45000.00 Create Date: 06/12/2018 02:02:35 PM Valid Until: 07/31/2018

Temp Inc: $45000.00 Create Date: 04/17/2018 11:16:15 AM Valid Until: 05/31/2018

Temp Inc: $45000.00 Create Date: 03/05/2018 03:22:05 PM Valid Until: 04/15/2018

Temp Inc: $45000.00 Create Date: 01/26/2018 10:11:25 AM Valid Until: 02/28/2018

Temp Inc: $45000.00 Create Date: 12/04/2017 12:29:15 PM Valid Until: 12/31/2017

Temp Inc: $45000.00 Create Date: 10/05/2017 01:07:02 PM Valid Until: 11/15/2017

Temp Inc: $45000.00 Create Date: 09/28/2017 01:07:31 PM Valid Until: 09/30/2017

Temp Inc: $45000.00 Create Date: 09/12/2017 10:51:58 AM Valid Until: 09/20/2017

Temp Inc: $45000.00 Create Date: 08/28/2017 02:42:30 PM Valid Until: 08/31/2017

Temp Inc: $45000.00 Create Date: 07/20/2017 11:50:52 AM Valid Until: 08/15/2017

Temp Inc: $35000.00 Create Date: 06/01/2017 11:09:22 AM Valid Until: 06/30/2017

Temp Inc: $0.00 Create Date: 05/18/2017 12:58:24 PM Valid Until: / /

Temp Inc: $40000.00 Create Date: 04/28/2017 10:22:30 AM Valid Until: 05/15/2017

Temp Inc: $40000.00 Create Date: 03/15/2017 04:03:46 PM Valid Until: 04/15/2017

Temp Inc: $40000.00 Create Date: 02/17/2016 12:40:34 PM Valid Until: 02/28/2016

Temp Inc: $40000.00 Create Date: 01/20/2016 10:18:17 AM Valid Until: 02/05/2016

Temp Inc: $40000.00 Create Date: 01/04/2016 01:00:56 PM Valid Until: 01/10/2016

Temp Inc: $40000.00 Create Date: 12/22/2015 11:09:30 AM Valid Until: 12/27/2015

Temp Inc: $40000.00 Create Date: 10/23/2015 05:50:08 PM Valid Until: 11/15/2015

Temp Inc: $40000.00 Create Date: 07/01/2015 12:46:00 PM Valid Until: 07/15/2015

Temp Inc: $40000.00 Create Date: 07/01/2015 12:45:15 PM Valid Until: 06/30/2015

Temp Inc: $80000.00 Create Date: 06/17/2015 11:02:56 AM Valid Until: 06/30/2015

Temp Inc: $80000.00 Create Date: 06/04/2015 12:36:38 PM Valid Until: 06/15/2015

Temp Inc: $80000.00 Create Date: 05/28/2015 10:02:29 AM Valid Until: 05/31/2015

Temp Inc: $110000.00 Create Date: 04/29/2015 10:39:27 AM Valid Until: 05/10/2015

Temp Inc: $100000.00 Create Date: 04/17/2015 10:48:49 AM Valid Until: 04/25/2015

Temp Inc: $100000.00 Create Date: 04/07/2015 02:56:32 PM Valid Until: 04/15/2015

Temp Inc: $90000.00 Create Date: 03/16/2015 02:08:44 PM Valid Until: 03/31/2015

Temp Inc: $65000.00 Create Date: 03/02/2015 01:58:15 PM Valid Until: 03/15/2015

Temp Inc: $65000.00 Create Date: 02/04/2015 03:42:57 PM Valid Until: 02/25/2015

Temp Inc: $40000.00 Create Date: 01/20/2015 02:57:20 PM Valid Until: 01/31/2015

Temp Inc: $35000.00 Create Date: 01/20/2015 02:56:42 PM Valid Until: 01/31/2015

Temp Inc: $35000.00 Create Date: 12/02/2014 01:37:26 PM Valid Until: 12/25/2014

Temp Inc: $35000.00 Create Date: 11/18/2014 10:54:34 AM Valid Until: 11/30/2014

Temp Inc: $35000.00 Create Date: 10/30/2014 11:36:20 AM Valid Until: 11/15/2014

Temp Inc: $35000.00 Create Date: 05/27/2014 04:31:00 PM Valid Until: 06/15/2014

Temp Inc: $35000.00 Create Date: 04/30/2014 12:03:50 PM Valid Until: 05/10/2014

Temp Inc: $35000.00 Create Date: 03/31/2014 12:09:30 PM Valid Until: 04/25/2014

Temp Inc: $35000.00 Create Date: 02/27/2014 01:43:29 PM Valid Until: 03/25/2014

Temp Inc: $35000.00 Create Date: 01/30/2014 11:26:18 AM Valid Until: 02/25/2014

Temp Inc: $35000.00 Create Date: 10/22/2013 12:24:15 PM Valid Until: 10/31/2013

Temp Inc: $35000.00 Create Date: 10/14/2013 03:59:27 PM Valid Until: 10/20/2013

Temp Inc: $35000.00 Create Date: 09/09/2013 11:25:24 AM Valid Until: 09/30/2013

Temp Inc: $30000.00 Create Date: 09/09/2013 11:25:13 AM Valid Until: 09/30/2013

Temp Inc: $30000.00 Create Date: 05/07/2013 01:19:58 PM Valid Until: 05/15/2013

Temp Inc: $30000.00 Create Date: 04/16/2013 01:04:38 PM Valid Until: 04/25/2013

Temp Inc: $50000.00 Create Date: 03/11/2013 02:27:48 PM Valid Until: 03/31/2013

Temp Inc: $50000.00 Create Date: 02/05/2013 04:39:51 PM Valid Until: 02/28/2013

Temp Inc: $35000.00 Create Date: 12/17/2012 04:05:35 PM Valid Until: 12/30/2012

Temp Inc: $35000.00 Create Date: 11/20/2012 01:51:24 PM Valid Until: 11/30/2012

Cred Limit: $25000.00 Eff. Date: 11/14/2012 10:52:36 AM

‘ WHERE ccustno=’TESTCUST01′ AND tmodified=’20231121 13:14:21.000’ AND ntempcrinc=10000.0000 AND mcrhistory LIKE ‘Temp Inc: $100000.00 Create Date: 09/28/2023 02:17:47 PM Valid Until: 10/31/2023