Repository navigation
Query regarding error behaviour with SqlBatch #3273
Description
Hello!
I had some queries regarding error handling with SqlBatch.
From my local tests I can observe the following behavior:
With default case - SqlBatch.Transaction is null, in case any statement in the batch fails, the changes from non-failing DMLs in the batch get committed.
QUESTION:
Is my understanding correct? Does the SqlBatch.Execute* API internally create a local transaction to rollback or commit the changes?
If SqlBatch.Transaction is NOT null, and a statement in the batch fails, even the non-failing DML changes are not committed.
QUESTION:
Is my understanding correct? Does the provider automatically rollback the changes in case an error is encountered? And will it automatically commit when no errors are there?
Thanks in advance.
Activity
Wraith2 commentedon Apr 14, 2025
Try it and see? I expect it depends on the exact definition of the sql server interaction between batches and transactions so thing like xact_abort can change it.
benrr101 commentedon Apr 22, 2025
In general your understanding is correct - however, if there is a transaction, it shouldn't be automatically committed. You would need to commit the transaction when you've finished executing the changes.
But as @Wraith2 said, you may need to try it and experiment a bit. We need to update our docs to give a better understanding of this behavior to developers.