This browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(41.6, 70%, 14%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3ED%3C/text%3E%3C/svg%3E)
hi we run 2014 enterprise. one of my sql scripts contains 2 updates and about 10 inserts.
i'm looking at its properties and 2 stand out, iso level serializable and tran option supported.
by any chance is this running as a single tran or do i need to wrap it in a tran or change the properties to accomolish that? I want it to roll back if there is a failure.
i'm tagging this question with sql server t-sql tag as well because the t-sql based solution proposed here seems inconsistent and i'm pretty certain wont be compatible with indexes that have computed columns.
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(105.60000000000001, 64%, 20%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3ERL%3C/text%3E%3C/svg%3E)
By default the Execute SQL task script doesn't create transaction. My suggestion is that if you need a transaction use normal SQL transaction handling (try catch, commit transaction, rollback, and set xact_abort on https://sqlskull.com/2020/02/22/sql-server-set-xact_abort/).
thanks erland and russel. it looks like my bad memory was confusing arithabort and xact_abort.
the former is the one that affects reads/writes etc that involve indexes with computed columns. and i'm not even sure it can affect things in more recent versions of sql (90 and beyond) even if explicitly set off.
my apologies.
oh yes, i think my lack of set based sql traces back to ssis's familiar problem with mapping variables to even slightly complex inline queries. thats easily overcome by making such queries a proc.
As a matter of fact, ARITHABORT does not affect anything at all, as long as ANSI_WARNINGS is ON.
What you may have in mind is that under compat level 80, ARITHABORT had to be ON for index on computed columns be considered, and having it OFF would also prevent writing to the table. Those days are gone. (For that setting, I should hasten to add. ANSI_NULLS, QUOTED_IDENTIFIER, CONCAT_NULL_YIELDS_NULL, ANSI_PADDING and ANSI_WARNING must still be ON. And NUMERIC_ROUNDABORT must be OFF.)
thx erland