SET XACT_ABORT: what the f***k

[kevin]

I got this error, for the first time today:
System.Data.SqlClient.SqlException: Unable to start a nested transaction for
OLE DB provider 'SQLOLEDB'. A nested transaction was required because the
XACT_ABORT option was set to OFF.

[OLE/DB provider returned message: Cannot start more transactions on this
session.]

OLE DB error trace [OLE/DB Provider 'SQLOLEDB'
ITransactionLocal::StartTransaction returned 0x8004d013: ISOLEVEL=4096].

I am moving data from server V3 to servers V4 and V5, using a linked server
via a SQL account on V3. I only do selects from V3 and all writes are to V4
or V5. All sp's use BEGIN TRAN (not BEGIN DISTRIBUTED TRAN) and error
handling to ROLLBACK or COMMIT. When writing data to two database on V5 I
get the above error, but no problems when writing to V4.

I checked Connections tab of the SQL SERVER Properties box of all three
severs and all default connection options are unchecked. I also checked all
SP's and the text XACT_ABORT does not exist.

Hell, I had never even heard of this option until today. My solution was to
add SET XACT_ABORT ON and OFF on each sp that executes against databases on
the V5 server.

1. What is the default value for XACT_ABORT?
2. Since it is a connection setting, could it be set in some db, login or
connectionstring option?
3. How do I turn it back on ?
4. Is it safe to just default all sp's with this set on?

gracias!!
kevin

[Erland Sommarskog]

Post by kevin
Hell, I had never even heard of this option until today. My solution
was to add SET XACT_ABORT ON and OFF on each sp that executes against
databases on the V5 server.

What happens with SET XACT_ABORT ON is that errors that normally would
terminate the statement only, instead aborts the batch and rolls back the
current transaction. Note that with this setting, some errors still do not
abort the batch, to wit compilation errors. (Which due to deferred name
resolution can occur at run-time as well.)

Post by kevin
1. What is the default value for XACT_ABORT?

OFF

Post by kevin
2. Since it is a connection setting, could it be set in some db, login or
connectionstring option?

In neither of these. But you can actually set it to be ON as a server-
wide default, by setting the configuration option to 16384 + its current
value. (That option is a bit mask.) I would not really recommend it,
though.

Post by kevin
3. How do I turn it back on ?

SET XACT_ABORT ON.

Post by kevin
4. Is it safe to just default all sp's with this set on?

Depends on what error handling you expect. If you are trying to log
errors, or use fallback actions in case of some errors XACT_ABORT is
not what you want.