Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Will H
Yak Posting Veteran
56 Posts |
 Posted - 2007-11-16 : 18:20:39
|
| Hi - I'm playing around and trying to get a grasp of transactions. I read that in a multi-statement transaction, a rollback would only occur on a statement that caused an error and not all the statement in the batch, unless XACT_ABORT was turned on. (if the above is simplified or not 100%, please correct. :) ) Anyway, this led to a few questions:1.) I can change XACT_ABORT on or off, but how do I check to see what its value currently is?2.) If I turn XACT_ABORT on at the top of a stored procedure or as the first line inside a transaction, how long does it stay on?? If another transaction/sp is called and it doesn't explicitly set XACT_ABORT on or off, what behavior can I expect? Same for if connection is reset. 3.) Any way (or reason) to keep this value consistently on for the lifetime of a database?---------------------------------------------------------SSRS Kills Kittens. |
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2007-11-16 : 18:28:59
|
| 1. DBCC USEROPTIONS: it will show as set if it's ON. If it's OFF then it will not show2. it stays on for the whole transaction. it's set per connection not per database or server3. this value is set for the duration of the connection._______________________________________________Causing trouble since 1980blog: http://weblogs.sqlteam.com/mladenpSSMS Add-in that does a few things: www.ssmstoolspack.com |
 |
|
|
|
|
|
XACT_ABORT question