I'm getting a conversion error when updating a table from OPENJSON. I'm not interesting in solving the conversion error as I'm testing the error handler for when the JSON has bad data. The transaction is getting aborted (XACT_STATE() = -1).
Is there some way to configure SQL not to abort the transaction?
BEGIN TRY BEGIN TRANSACTION
-- more code ...
-- Update first table from JSON UPDATE t SET [field] = [value] FROM dbo.table t CROSS APPLY OPENJSON(t.payload, '$') WITH ([field] [type], ...)
-- Update second table from JSON UPDATE t SET [field] = [value] FROM dbo.table t CROSS APPLY OPENJSON(t.payload, '$') WITH ([field] [type], ...)
-- more code ...
END TRY BEGIN CATCH -- Handle error -- Rollback and start a new transaction if necessary. END CATCH
COMMIT TRANSACTION