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

Kinetic ERP
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