Ms SQL Server transaction rollback!

Syntax
Set xact_abort {on | off}

When set xact_abort is on, if a running error occurs in a Transact-SQL statement, the entire transaction is terminated and rolled back. When it is off, only the wrong Transact-SQL statement is returned, and the transaction will continue to be processed. Compilation errors (such as syntax errors) are not affected by set xact_abort.

For most ole db providers (including SQL Server), xact_abort must be set to on for data modification statements in implicit or explicit transactions. The only case where this option is not required is when the Provider supports nested transactions. For more information, see distributed queries and distributed transactions.

Set xact_abort is set during execution or running, rather than during analysis. # Database Technology