Unable to update part record - "Roll back the transaction." SQL error

Kinetic ERP
1

I’ve run into a few parts in our database that can’t be updated. When doing so (eg changing part description and clicking save), they give a server-side Epicor error. See it below. Other parts have no issue.

There is a related KB article saying the part may have an invalid description and is tripping up the sql query. But even if I blank all the text fields on the part, I’m still unable to save.

The error is also present in our test database that is several weeks old, on the same specific parts. ChangedOn for the records is ~2018. So I don’t think it is something recent.

When I try to run a SQL update command on that part (in test), I get the error too.

Msg 3930, Level 16, State 1, Procedure TR_Part_ChangeCapture, Line 29 [Batch Start Line 0]
The current transaction cannot be committed and cannot support operations that write to the log file. Roll back the transaction.

There are lots of web pages discussing that error, though I don’t see anything I can follow in the context of Epicor… so help?

Thanks much

Ice.Common.EpicorServerException: BPM runtime caught an unexpected exception of ‘SqlException’ type. See more info in the Inner Exception section of Exception Details.
—> System.Data.SqlClient.SqlException: The current transaction cannot be committed and cannot support operations that write to the log file. Roll back the transaction.
The statement has been terminated. at Ice.TablesetBound3.UpdateRow(IceDataContext dataContext, Int32 tableNum, IIceTable table, IceRow updatedRow, IceRow originalRow, IColumnUncensor uncensor, TablesetProfilingCollector parentTraceCollector) in C:\_releases\ICE\ICE4.1.200.13\Source\Server\Framework\Epicor.Ice\Services\TablesetBound.cs:line 1294 at Ice.TablesetBound3.WriteTable(IceDataContext dataContext, Int32 tableIndex, IIceTable table, TablesetProfilingCollector parentTraceCollector) in C:_releases\ICE\ICE4.1.200.13\Source\Server\Framework\Epicor.Ice\Services\TablesetBound.cs:line 979 at Ice.TablesetBound3.InnerUpdate[TUpdater](IceDataContext dataContext, TFullTableset tableset) in C:\_releases\ICE\ICE4.1.200.13\Source\Server\Framework\Epicor.Ice\Services\TablesetBound.cs:line 871 at Erp.Services.BO.PartSvc.Update(PartTableset& ds) in C:\_releases\ERP\ERP11.1.200.13\Source\Server\Services\BO\Part\Part.Designer.cs:line 6051 at Epicor.Customization.Bpm.MethodCustomizationBase22.RunDirectives(TParam parameters) in C:_releases\ICE\ICE4.1.200.0\Source\Server\Internal\Lib\Epicor.Customization.Bpm\MethodCustomizationBase2.cs:line 179 at Epicor.Customization.Bpm.CustomizationBase22.Execute(TParam parameters) in C:\_releases\ICE\ICE4.1.200.0\Source\Server\Internal\Lib\Epicor.Customization.Bpm\CustomizationBase2.cs:line 87 at Epicor.Customization.Bpm.BO.PartSvcCustomization.Update(PartTableset& ds) at Erp.Services.BO.PartSvcFacade.Update(PartTableset& ds) in C:\_releases\ERP\ERP11.1.200.13\Source\Server\Services\BO\Part\PartSvcFacade.cs:line 6447 at Epicor.Customization.Bpm.DirectiveBase2.CallService[TService](Action1 action) in C:\_releases\ICE\ICE4.1.200.0\Source\Server\Internal\Lib\Epicor.Customization.Bpm\DirectiveBase.Generic.Plugins.cs:line 310 at Epicor.Customization.Bpm.BO.UpdatePreProcessingDirective_AltDMT_LSS_to_NPS_EAB1C7032B8C4EEFA477A7B999F8180E.ExecuteCore(Int32 step) at Epicor.Customization.Bpm.DirectiveBase2.Execute() in C:_releases\ICE\ICE4.1.200.0\Source\Server\Internal\Lib\Epicor.Customization.Bpm\DirectiveBase.Generic.cs:line 330 at Epicor.Customization.Bpm.DirectiveBase2.Execute(TParam parameters) in C:\_releases\ICE\ICE4.1.200.0\Source\Server\Internal\Lib\Epicor.Customization.Bpm\DirectiveBase.Generic.cs:line 180 --- End of inner exception stack trace --- at Epicor.Customization.Bpm.DirectiveBase2.Execute(TParam parameters) in C:_releases\ICE\ICE4.1.200.0\Source\Server\Internal\Lib\Epicor.Customization.Bpm\DirectiveBase.Generic.cs:line 201 at Epicor.Customization.Bpm.MethodCustomizationBase22.<>c__DisplayClass11_0.<RunDirectives>b__3(MethodDirectiveBase2 dir) in C:_releases\ICE\ICE4.1.200.0\Source\Server\Internal\Lib\Epicor.Customization.Bpm\MethodCustomizationBase2.cs:line 135 at System.Linq.Enumerable.FirstOrDefault[TSource](IEnumerable1 source, Func2 predicate) at Epicor.Customization.Bpm.MethodCustomizationBase22.RunDirectives(TParam parameters) in C:\_releases\ICE\ICE4.1.200.0\Source\Server\Internal\Lib\Epicor.Customization.Bpm\MethodCustomizationBase2.cs:line 137 at Epicor.Customization.Bpm.CustomizationBase22.Execute(TParam parameters) in C:_releases\ICE\ICE4.1.200.0\Source\Server\Internal\Lib\Epicor.Customization.Bpm\CustomizationBase2.cs:line 87 at Epicor.Customization.Bpm.BO.UserCodesSvcCustomization.Update(UserCodesTableset& ds) at Ice.Services.BO.UserCodesSvcFacade.Update(UserCodesTableset& ds) in C:_releases\ICE\ICE4.1.200.0\Source\Server\Services\BO\UserCodes\UserCodesSvcFacade.cs:line 414 at SyncInvokeUpdate(Object , Object , Object ) at System.ServiceModel.Dispatcher.SyncMethodInvoker.Invoke(Object instance, Object inputs, Object& outputs) at Epicor.Hosting.OperationBoundInvoker.InnerInvoke(Object instance, Func2 func) in C:\_releases\ICE\ICE4.1.200.13\Source\Server\Framework\Epicor.System\Hosting\OperationBoundInvoker.cs:line 59 at Epicor.Hosting.OperationBoundInvoker.Invoke(Object instance, Func2 func) in C:_releases\ICE\ICE4.1.200.13\Source\Server\Framework\Epicor.System\Hosting\OperationBoundInvoker.cs:line 28

2

Whew figured it out. It was a bad character (0x1e, invisible) in the PurComment field. But when trying to clear this field, it wouldn’t work due to the CDC log trying to convert that character to xml. So I temporarily turned off CDC for the part table in CDC management, deleted the bad character, and it worked. The bad characters must have been put into the database before we had CDC, or had it turned on for the part table.

Debugging guidance for others:

  1. Replicate issue in Test database, don’t do below in your live…
  2. Turn off table trigger in SSMS, confirm issue gone
  3. Debug trigger code. in this case, it was
CAST(( ... FOR XML AUTO, ELEMENTS, ROOT('Erp.Part')) AS XML)

Which fails on 0x1e characters. When running test code directly it gives better error messages, can find the bad field.

  1. Locate bad records in database. I used:
SELECT string_escape(PurComment, 'json') , * 
FROM Part where string_escape(PurComment, 'json') like '%\u001e%'
  1. Found safe way to turn off CDC trigger in live database (cdc management).
1 Like