I have sql code set up like this:
set xact_abort on
declare @ErrorMessage nvarchar(4000)
declare @ErrorSeverity int
declare @ErrorState int
Transaction 1 -- insert
Transaction 2 ---insert
if (XACT_STATE ()) = -1
if (XACT_STATE ()) = 1
select @ErrorMessage = ERROR_MESSAGE(),
@ErrorSeverity = ERROR_SEVERITY(),
@ErrorState = ERROR_STATE()
@ErrorMessage, -- Message text.
@ErrorSeverity, -- Severity.
@ErrorState -- State.
I'm experiencing following symptoms:
Transaction 2 is succesfull (the data is inserted into a table), and transaction 1 is "skipped".
The code is in sp and there are no nested transactions. It's not called from an application but rather from a job.
There are no raised errors.
What's wrong with this folks?