If you’d like to watch a video explaining this, see below or click here.
We used SmartConnect to create a batch of Payables Transactions.
By default, eConnect creates all batches as Single Use as shown below. Single Use means that if the batch posts successfully, the batch record in the SY00500 table is deleted.
However, we needed the batches to be recurring, so we manually changed the batch in Microsoft Dynamics GP from Single Use to one of the Recurring batch types.
When posting the batch in GP, it fails with an error and drops into Batch Recovery.
The error message is:
Batch SC_RECURRING failed to complete posting. Us the Batch Recovery window to complete the posting process.
The More Info button shows:
A save operation on table ‘Batch_Headers’ record was changed by another user.
If I create a new recurring batch manually in GP and move the transactions to it, the batches post fine. Why doesn’t it work when from SmartConnect/eConnect?
Microsoft Dynamics GP Application Background Affecting This Issue
In GP/Dexterity, it uses a method called ‘Optimistic Concurrency Control‘, which would allow two GP Users to edit the same record at the same time.
For example, User1 could update Customer AARONFIT0001 and change the Customer Name and User2 could update the Contact and both would be able to save the record successfully with GP updating just the one field for each user.
For this instance, Dexterity runs the update statement on the table to restrict by the primary key and then also for any fields that were changed.
In the example for User2, changing the Contact to “Patrick” and saving the record in GP would result in a SQL Update statement looking similar to:
UPDATE RM00101 SET CONTACT = 'Patrick' where CUSTNMBR = 'AARONFIT0001' and CONTACT = 'Bob Fitz'
The CUSTNMBR is the primary key on the table, so naturally Dexterity/GP would use that value in the restriction, but it also restricts to the changed field(s) to the previous value to see if it was changed outside of that user session.
If the statement runs and fails to find a record to update, since the record did exist but now cannot be found, that would mean that another user changed the CONTACT field as well.
If both User1 and User2 had the same record open, and both changed the same field, the first user to save the record “wins”, the second user to save the record “loses” and gets the error message “This record has been changed by another user,” and the changes are not saved.
In the case of batch posting, we wouldn’t expect another user to have changed the record, but it is possible that a database trigger or a customization might be causing this error.
Investigation
Since I was able to reproduce the issue, it seems unlikely that it was a customization causing the issue, but a SQL Profile Trace did reveal the underlying reason.
In the SQL Profile Trace screenshot above, this is the part where the posting has finished and Dynamics GP is going to update the SY00500 record to update the Date and TIME1 field since this is a recurring batch.
The ObjectName does not show the name of a database trigger, so this would be the GP SQL code doing the update vs a SQL Database Trigger.
On the UPDATE TWO.dbo.SY00500 line highlighted, the RowCount of affected records is zero. The expected result should have been one. Due to this result, Dexterity believes this happened due to another user changing the record between the read & the update and throws the “Record Changed” error seen.
Reviewing the “where” clause, which is the PK fields of the record and any other fields that were changed as part of posting: the Date & TIME1 fields.
Inspecting the TIME1 field, the problem is revealed.
The batch that eConnect creates has milliseconds in it, while the other manually created record, the TIME1 field, does not.
Going back to the SQL Profile trace, the @P12 TIME1 variable is set to ‘1900-01-01 10:35:12’
Dexterity Date and Time fields do not use milliseconds and so Dexterity reads to the second, but because the actual data has the milliseconds, SQL cannot find the record, so the record changed error is given.
For non-recurring batches, the TIME1 field is not changed by posting, and therefore the same error will not happen for those batches.
Resolution for Recurring Payables Batch Fails
This seems to be a bug in the eConnect taCreateUpdateBatchRecord that only affects Recurring batches being posted.
Out of the box, when creating a transaction type document, eConnect calls the taCreateUpdateBatchRecord, which populates the TIME1 field with the current time, which includes the milliseconds, causing this issue.
The best way to work around the problem from SmartConnect perspective would be to use an ‘after document success’ SQL Task.
The “use GlobalCompanyID” is to make sure that no matter what the destination GP database of the integration is, the correct database context is used. The Batch Source would have to be changed to the correct value for the transaction type you are integrating to, if not Payables Transactions. Also, use the Insert Variable lookup for the field to get the correct field name, and take care not to let SmartConnect put in leading or trailing spaces inside the single quotes.
The SQL Script from the screenshot is:
use GlobalCompanyID update SY00500 set TIME1 = DATEADD(ms, -DATEPART(ms, TIME1), TIME1) where BCHSOURC = 'PM_Trxent' and BACHNUMB = '_BatchID'
Once this SQL Task was added to the integration to fix the TIME1 field, the newly created batches posted as expected in GP.
If you run into any trouble while resolving recurring Payables batch fails in Microsoft Dynamics GP, please contact support@eonesolutions.com for assistance.
