How to make SET XACT_ABORT ON rollback the transaction?
The SQL Server only rollback transactions when Severity level greater or equals 16.
See example:
Msg 544, Level 16, State 1, Line 1
Cannot insert explicit value for identity column in table 'ORC_ORCAMENTO' whenIDENTITY_INSERT is set to OFF.
Test on SQL Server 2008 R2
SET XACT_ABORT ON;
BEGIN TRANSACTION;
PRINT 'TranCount befor an error = '+CAST(@@Trancount AS varchar(50))
insert into ORC_ORCAMENTO (ORCID, ORCNOME, ORCATIVO) VALUES (1, 'TESTE_ALEXP', 0);
PRINT 'TranCount after an error = '+CAST(@@Trancount AS varchar(50))
Returns
TranCount befor an error = 1
Msg 544, Level 16, State 1, Line 5
Cannot insert explicit value for identity column in table 'ORC_ORCAMENTO' when IDENTITY_INSERT is set to OFF.
TranCount after an error = 0
See Microsoft Error Message Levels on
https://docs.microsoft.com/en-us/sql/relational-databases/errors-events/database-engine-events-and-errors