InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comSun, 19 May 2013 19:14:03 GMT20http://www.sqlservercentral.com/Forums/Topic1304651-1222-1.aspxI answered 1 row.Assuming violation is at second row so first row will be xcuted succesffully. One more thing i didn't notice that all three insert statements are inside a transaction. But if it is implict transaction and set xact_abort is ONThen first row will be inserted sucessfully..Wed, 09 Jan 2013 05:06:43 GMTDineshbabuhttp://www.sqlservercentral.com/Forums/Topic1304651-1222-1.aspxwrong answer :(I learned a lot from transaction questions :)Mon, 08 Oct 2012 00:50:39 GMTkapil_kkhttp://www.sqlservercentral.com/Forums/Topic1304651-1222-1.aspxGood question.Thu, 09 Aug 2012 09:00:07 GMTNeha05http://www.sqlservercentral.com/Forums/Topic1304651-1222-1.aspxI got it right for the wrong reason. Learned something. Thanks for submitting.Thu, 24 May 2012 19:38:48 GMTBritt Cluffhttp://www.sqlservercentral.com/Forums/Topic1304651-1222-1.aspxGreat question! You'd be surprised to know how many SQL developers think if any error is encountered within a transaction it automatically gets rolled back like when XACT_ABORT is on.Thu, 24 May 2012 11:16:27 GMTDoug 53730http://www.sqlservercentral.com/Forums/Topic1304651-1222-1.aspx[quote][b]ralph.bacon (5/24/2012)[/b][hr][quote][b]rmechaber (5/23/2012)[/b][hr]...I found this write-up useful in explaining the benefits -- and issues -- related to XACT_ABORT and TRY..CATCH: [url=http://www.simple-talk.com/sql/t-sql-programming/defensive-error-handling/]http://www.simple-talk.com/sql/t-sql-programming/defensive-error-handling/[/url].For example, there's a nice demonstration of how XACT_STATE() is needed to catch uncommittable transactions, following something as simple as a failed type conversion from string to integer [SELECT CAST('abc' AS INTEGER)].Rich[/quote]An excellent article, that should be compulsory reading for all us SQL developers!Thanks for posting Rich.[/quote]YW, thanks for letting me know you found it useful!RichThu, 24 May 2012 06:07:18 GMTrmechaberhttp://www.sqlservercentral.com/Forums/Topic1304651-1222-1.aspxNice question i got it correct,i encountered a similar experience.I liked the explanation learned something.thank you.Thu, 24 May 2012 03:25:21 GMTkapfundestanleyhttp://www.sqlservercentral.com/Forums/Topic1304651-1222-1.aspx[quote][b]Hugo Kornelis (5/23/2012)[/b][hr]If you want the application to be outside SQL Server, you should ... even control the transactions from the application.[/quote]That's what we do (MTS in our case). Has its down sides, but allows all the error handling and logging to be done in the application.Thu, 24 May 2012 02:59:52 GMTToreadorhttp://www.sqlservercentral.com/Forums/Topic1304651-1222-1.aspxI have to say that I am astonished that I was not crystal clear on this(I got it wrong), after the years I have programmed against SQL Server. In mitigation I am an application developer and thus less likely to work directly in the way this question operates (i.e. I would control transactions externally in most cases). Still, this is why I keep doing the QOTD to be reminded of my limitations - great discussion which has really clarified the whole tx thing and the reasons behind how it works the way it does, thanks.Thu, 24 May 2012 02:48:19 GMTcall.copsehttp://www.sqlservercentral.com/Forums/Topic1304651-1222-1.aspxNice question and nice conversation as always :-)Thu, 24 May 2012 02:24:51 GMTHardy21http://www.sqlservercentral.com/Forums/Topic1304651-1222-1.aspxNasty, but great question. Tricked me! :cool:Thu, 24 May 2012 02:08:54 GMTMichael Ghttp://www.sqlservercentral.com/Forums/Topic1304651-1222-1.aspx[quote][b]rmechaber (5/23/2012)[/b][hr]...I found this write-up useful in explaining the benefits -- and issues -- related to XACT_ABORT and TRY..CATCH: [url=http://www.simple-talk.com/sql/t-sql-programming/defensive-error-handling/]http://www.simple-talk.com/sql/t-sql-programming/defensive-error-handling/[/url].For example, there's a nice demonstration of how XACT_STATE() is needed to catch uncommittable transactions, following something as simple as a failed type conversion from string to integer [SELECT CAST('abc' AS INTEGER)].Rich[/quote]An excellent article, that should be compulsory reading for all us SQL developers!Thanks for posting Rich.Thu, 24 May 2012 01:07:36 GMTralph.baconhttp://www.sqlservercentral.com/Forums/Topic1304651-1222-1.aspxGood question and great discussion. Thanks!Wed, 23 May 2012 19:18:31 GMTRobin Sassonhttp://www.sqlservercentral.com/Forums/Topic1304651-1222-1.aspxI picked the right answer of 2 rows, but it was not because of the Autocommit feature but there is no error checking done - either checking for @@ERROR or a TRY/CATCH. This means every statement after the one that caused the duplicate error is still going to be executed.This is whay the 3rd insert inserted the 2nd record and then the transaction was commited.:-DWed, 23 May 2012 17:35:29 GMTjohn-byrnehttp://www.sqlservercentral.com/Forums/Topic1304651-1222-1.aspx[quote][b]roger.plowman (5/23/2012)[/b][hr][quote][b]L' Eomot Inversé (5/23/2012)[/b][hr][quote][b]roger.plowman (5/23/2012)[/b][hr]Having the default setting to OFF basically break transactions and is a *bug* in SQL Server.Explicit transactions should be exactly that, explicit. [/quote]That's the whole point of course: they are explicit. Commitment is done explicitly, has to be coded. Rollback, unless the developer/dba specifies otherwise or something really nasty happens, is also done explicitly and has to be coded. If you want trivial errors to do roll-back automatically, rather than signalling an error inside the transaction context so as to enable your error handling code to see the modifications made so far by the transaction, you have the option of making that happen (set XACT_ABORT ON); people who are used to managing errors and doing error correction where possible and error containment where correction is impossible will usually want the default behaviour. [quote]This stupidity is exactly the kind of thing that will cause nearly untraceable bugs in code. What were they thinking?[/quote]Perhaps they were thinking about enabling the developer to do proper error management? The only stupidity here is calling an extremely useful feature )one that enables far more robust things to be developed) a "bug".[/quote]You assume that the T/SQL code could correctly handle the error. [/quote]No, I make no such assumption. I look at the code in the question and see a number of things:-(i) the T-SQL code in the database, not some higher layer, determines the transaction boundaries.(ii) the T-SQL code for the insertion is not a single query, as it could easily have been, it is multiple statements. Presumably the reason for that is that the semantics of multiple inserts is required, rather than the semantics of single row inserts.(iii) From the two points above, it's quite clear that the T-SQL component has responsability for some of the application logic.That's deduction from very clear evidence, not =any sort of assumption.[quote]For many applications this is not a good assumption, since the database will not know information the application does, information usually necessary to correct the issue (which is usually some kind of user error).[/quote]Where the SQL layer doesn't know enough to do anything useful with an error, XACT_ABORT should be on. As someone once said (back in sql 2000 days), the correct spelling of "BEGIN TRAN" in those circumstances is "SET XACT_ABORT ON; BEGIN TRAN". However, this can make real time error containment very difficult, since the information that gets back to the application will generally be a pretty general purpose error message (one that's built into the data engine); it is often very useful for the SQL layer to generate some extra information (like how far the transaction got, and perhaps which data values or parameters led to the error) that will enable the application to do better real time error containment and/or recovery, even if it can't do the containment or recovery itself. It is a good general rule that information describing the error and how it came about should be generated as low down in the system as is possible, so that containment and recovery can be effective.Since 2005 we've had modern (ie post-Cobol) error handling in T-SQL, with TRY-CATCH, so it is now very easy to run some code in a catch block within the transaction after the error to collect (in variables, perhaps including table variables, of course) extra information about the state of the world at the time of the error and then after rolling the transaction back and rethrowing the error either log that information or pass it out to the application, while in the bad old days we had to look at @@error and mess about with IFs to achieve a similar effect.[quote]If an application hands SQL Server a transaction to process the expected behavior is the transaction will either succeed or fail. There should be no "massaging" of the data to "correct" problems (ie no error handling should be expected on the SQL Server side).[/quote]What, not even reporting to the application that there has been an error? That is error handling, you know.[quote]That's not the database engine's job. The engine should only be concerned whether the data matched all the constraints or not. Anything beyond that is the application's responsibility--which includes error handling. And when I say "application" I mean an external application competely seperate from SQL Server.[/quote]But constraints (other than auto-generated primary keys) are determined by the business rules for the applications, so you are saying the database engine must not tcheck foreign key constraints, enforce default constraints, check unique and not null constraints, or check any primary key constraint other than for an automatically generated primary key. Rolling back a transaction when a unique constraint is violated (which will happen if XACT_ABORT is on) is error handling, and it is also something you are claiming the database engine ought always to do. So you can't assert reasonably that it must do no error handling.[quote]After all, in the example today the database allowed a *partial* transaction to commit (by default!). That violates the definition of a transaction--and thus is a bug, not a feature.[/quote]Your choice of phase, "partial transaction", is rather strange when used for a transaction which has been committed. If you decide to invent terms like that you can pick ones that appear to support any position you choose, but I certainly don't regard that as eitherlogical or persuasive. The definition of a transaction is that it is a discrete piece of work that has the usual ACID properties. Can you explain why you believe that the transaction presented in this QoD fails to possess those properties, so that it violates that definition?Maybe you want T-SQL to be a language in which it is impossible to write bad code (of which the code in the QoD is probably an example, as it has a multi-statement explicit transaction without any attempt to handle statement-level errors). It would be nice to have such a language, but I don't know anyone who has worked on computer languages who believes that such a language is possible.Wed, 23 May 2012 17:00:24 GMTL' Eomot Inverséhttp://www.sqlservercentral.com/Forums/Topic1304651-1222-1.aspx[quote][b]roger.plowman (5/23/2012)[/b]After all, in the example today the database allowed a *partial* transaction to commit (by default!). That violates the definition of a transaction--and thus is a bug, not a feature.[/quote]No, it didn't allow a partial transaction to commit by default. The batch contained explicit begin/commit transaction statement so SQLServer did exactly as the developer told it to. SQLServer did not decide to commit the transaction automatically, it executed the commit statement in the code, so the code written by the developer did it. That's not a bug in SQLServer, it's a bug in the developers code assuming this is not what they want to happen.You seem to be getting this whole QOD confused with implicit transactions and autocommit. This is not an implicit, autocommit scenario; the code contains an explicit begin/commit with no error handling and so the code did exactly as the documentation says it will do. The error was at the statement level and so needs error handling if you want to rollback the entire transaction. If you are silly enough to use an explicit commit in code without error handling don't blame SQLServer for the result. A poor workman blames his tools. You need to understand how the tools work and use them accordingly. There is no bug, just poorly written SQL in this case, which is the point the QOD was trying to make.CheersRoddyWed, 23 May 2012 16:28:07 GMTRoddy.CAMERONhttp://www.sqlservercentral.com/Forums/Topic1304651-1222-1.aspxFunny...I knew the answer from having attended Denise's class on transactions at the San Diego SQL Saturday last fall.Wed, 23 May 2012 16:22:29 GMTjodom 48527http://www.sqlservercentral.com/Forums/Topic1304651-1222-1.aspx[quote][b]roger.plowman (5/23/2012)[/b][hr]And when I say "application" I mean an external application competely seperate from SQL Server.[/quote]If you want the application to be outside SQL Server, you should avoid all programmability features T-SQL offers you and standalone queries only. You should even control the transactions from the application.Conversely, if you use T-SQL to code logic beyond the basic database building block (query), you are bringing part of the application to SQL Server.Wed, 23 May 2012 14:27:26 GMTHugo Kornelishttp://www.sqlservercentral.com/Forums/Topic1304651-1222-1.aspx[quote][b]L' Eomot Inversé (5/23/2012)[/b][hr][quote][b]roger.plowman (5/23/2012)[/b][hr]Having the default setting to OFF basically break transactions and is a *bug* in SQL Server.Explicit transactions should be exactly that, explicit. [/quote]That's the whole point of course: they are explicit. Commitment is done explicitly, has to be coded. Rollback, unless the developer/dba specifies otherwise or something really nasty happens, is also done explicitly and has to be coded. If you want trivial errors to do roll-back automatically, rather than signalling an error inside the transaction context so as to enable your error handling code to see the modifications made so far by the transaction, you have the option of making that happen (set XACT_ABORT ON); people who are used to managing errors and doing error correction where possible and error containment where correction is impossible will usually want the default behaviour. [quote]This stupidity is exactly the kind of thing that will cause nearly untraceable bugs in code. What were they thinking?[/quote]Perhaps they were thinking about enabling the developer to do proper error management? The only stupidity here is calling an extremely useful feature )one that enables far more robust things to be developed) a "bug".[/quote]You assume that the T/SQL code could correctly handle the error. For many applications this is not a good assumption, since the database will not know information the application does, information usually necessary to correct the issue (which is usually some kind of user error).If an application hands SQL Server a transaction to process the expected behavior is the transaction will either succeed or fail. There should be no "massaging" of the data to "correct" problems (ie no error handling should be expected on the SQL Server side).That's not the database engine's job. The engine should only be concerned whether the data matched all the constraints or not. Anything beyond that is the application's responsibility--which includes error handling. And when I say "application" I mean an external application competely seperate from SQL Server.After all, in the example today the database allowed a *partial* transaction to commit (by default!). That violates the definition of a transaction--and thus is a bug, not a feature.Wed, 23 May 2012 12:55:50 GMTroger.plowmanhttp://www.sqlservercentral.com/Forums/Topic1304651-1222-1.aspxgood question - cheersWed, 23 May 2012 11:32:40 GMTOzYbOi d(-_-)bhttp://www.sqlservercentral.com/Forums/Topic1304651-1222-1.aspxGood question. I learned something, too.But I also agree with the following...[quote][b]L' Eomot Inversé (5/23/2012)[/b][hr]Unfortunately the explanation is all about implicit transactions, while the question is about an explicit transaction. So the explanation is not exactly helpful![/quote]Wed, 23 May 2012 11:02:27 GMTRob Schripsemahttp://www.sqlservercentral.com/Forums/Topic1304651-1222-1.aspxInteresting question... Thanks one more time, Ron!Wed, 23 May 2012 10:40:12 GMTRevenanthttp://www.sqlservercentral.com/Forums/Topic1304651-1222-1.aspx[quote][b]roger.plowman (5/23/2012)[/b][hr]Having the default setting to OFF basically break transactions and is a *bug* in SQL Server.Explicit transactions should be exactly that, explicit. [/quote]That's the whole point of course: they are explicit. Commitment is done explicitly, has to be coded. Rollback, unless the developer/dba specifies otherwise or something really nasty happens, is also done explicitly and has to be coded. If you want trivial errors to do roll-back automatically, rather than signalling an error inside the transaction context so as to enable your error handling code to see the modifications made so far by the transaction, you have the option of making that happen (set XACT_ABORT ON); people who are used to managing errors and doing error correction where possible and error containment where correction is impossible will usually want the default behaviour. [quote]This stupidity is exactly the kind of thing that will cause nearly untraceable bugs in code. What were they thinking?[/quote]Perhaps they were thinking about enabling the developer to do proper error management? The only stupidity here is calling an extremely useful feature )one that enables far more robust things to be developed) a "bug".Wed, 23 May 2012 10:20:51 GMTL' Eomot Inverséhttp://www.sqlservercentral.com/Forums/Topic1304651-1222-1.aspxThanks for the question.Wed, 23 May 2012 10:09:58 GMTSQLRNNRhttp://www.sqlservercentral.com/Forums/Topic1304651-1222-1.aspxOops, I see several other people have already proposed what I just wrote. I didn't realise this thread was so long, apologies!Wed, 23 May 2012 08:45:12 GMTralph.baconhttp://www.sqlservercentral.com/Forums/Topic1304651-1222-1.aspxIn which case encapsulating the transaction with a BEGIN TRY/CATCH and a ROLLBACK TRANSACTION in the CATCH does do what a lot of people apparently expected here.I shall have to investigate the XACT_ABORT, that's interesting.Wed, 23 May 2012 08:41:36 GMTralph.baconhttp://www.sqlservercentral.com/Forums/Topic1304651-1222-1.aspxMight be slightly off-topic, but I found this write-up useful in explaining the benefits -- and issues -- related to XACT_ABORT and TRY..CATCH: [url=http://www.simple-talk.com/sql/t-sql-programming/defensive-error-handling/]http://www.simple-talk.com/sql/t-sql-programming/defensive-error-handling/[/url].For example, there's a nice demonstration of how XACT_STATE() is needed to catch uncommittable transactions, following something as simple as a failed type conversion from string to integer [SELECT CAST('abc' AS INTEGER)].RichWed, 23 May 2012 08:12:14 GMTrmechaberhttp://www.sqlservercentral.com/Forums/Topic1304651-1222-1.aspx[quote][b]Nakul Vachhrajani (5/22/2012)[/b][hr]Good question with a couple of learning points.1. By default SQL does not abort the entire transaction if it encounters a statment-level failure. In this case, the primary key violation is a statement level failure and hence, only the statement terminated, not the entire transactionIf the requirement is to cause a failure of the entire transaction (i.e. abort), then the following SET option needs to be set to ON:[code="sql"]SET XACT_ABORT ON[/code]Setting XACT_ABORT to ON will cause SQL Server to abort the transaction even if it encounteres the statement level failure.2. Referring one of my blog posts, row constructors (introduced in SQL Server 2008) process the entire batch at once. Hence, had this example used row constructors, the correct answer would have been 0 rows affected (Reference post: [url=http://beyondrelational.com/modules/2/blogs/77/Posts/14434/0159-sql-server-row-constructors-triggers-process-the-entire-batch-at-once.aspx]http://beyondrelational.com/modules/2/blogs/77/Posts/14434/0159-sql-server-row-constructors-triggers-process-the-entire-batch-at-once.aspx[/url])Thank-you![/quote][b]THIS[/b] should have been the explanation. The explanation given for the question explains nothing.Wed, 23 May 2012 08:00:25 GMTsknoxhttp://www.sqlservercentral.com/Forums/Topic1304651-1222-1.aspx[quote][b]Hugo Kornelis (5/23/2012)[/b][hr][quote][b]mtassin (5/23/2012)[/b][hr]Sorry, I worry more about what goes into or out of a table... the select statement at the end was an after thought. If the intention was that none of the data goes in if one of the inserts fails, then the try/catch blocks above also give that result.[/quote]Yes, I understand.My point is that using a TRY ... CATCH block is [i]better[/i] than using XACT_ABORT, because it gives you much more control over how errors are handled and what information is returned to the client.[/quote]Oh I agree... We got TRY/CATCH 4 versions of SQL ago (2005, 2008, 2008R2, 2012 by my count)It wasn't until recently that we've really started to use it more to its fullest... Too many years with SQL 2000 makes using new features tough.... :)Wed, 23 May 2012 07:55:32 GMTmtassinhttp://www.sqlservercentral.com/Forums/Topic1304651-1222-1.aspxThanks for the question!Wed, 23 May 2012 07:52:31 GMTDalton Moorehttp://www.sqlservercentral.com/Forums/Topic1304651-1222-1.aspx[quote][b]mtassin (5/23/2012)[/b][hr]Sorry, I worry more about what goes into or out of a table... the select statement at the end was an after thought. If the intention was that none of the data goes in if one of the inserts fails, then the try/catch blocks above also give that result.[/quote]Yes, I understand.My point is that using a TRY ... CATCH block is [i]better[/i] than using XACT_ABORT, because it gives you much more control over how errors are handled and what information is returned to the client.Wed, 23 May 2012 07:48:11 GMTHugo Kornelishttp://www.sqlservercentral.com/Forums/Topic1304651-1222-1.aspx[quote][b]Hugo Kornelis (5/23/2012)[/b][hr][quote][b]mtassin (5/23/2012)[/b][hr]But this would also work as though xact_abort is on :)[code]BEGIN trybegin TRANSACTIONINSERT qotd2(col1,col2,col3) VALUES (1,'x','some')INSERT qotd2(col1,col2,col3) VALUES (1,'Y','thing')INSERT qotd2(col1,col2,col3) VALUES (2,'Z','or other')COMMITEND TRYBEGIN CATCHROLLBACKEND catchSELECT * FROM qotd2[/code][/quote]No, it would not. With XACT_ABORT set to ON, the final SELECT statement would never be executed. XACT_ABORT causes the batch to be terminated on an error.[/quote]Sorry, I worry more about what goes into or out of a table... the select statement at the end was an after thought. If the intention was that none of the data goes in if one of the inserts fails, then the try/catch blocks above also give that result.Wed, 23 May 2012 07:34:37 GMTmtassinhttp://www.sqlservercentral.com/Forums/Topic1304651-1222-1.aspx[quote][b]Hugo Kornelis (5/23/2012)[/b][hr]I guess that depends on the expectation. The code in this QotD contains no error-handling and an unconditional COMMIT statement.[/quote]My expectation is that those two conditions never exist in production code.Either begin a transaction and handle errors correctly (which might be to rollback) or don't incur the overhead of the transaction in the first place. Our typical ad-hoc queries have the commit before the begin transaction and commented out so it can't be run with a batch of code. Instead it is run after successful execution, with the understanding that any error in a batch means something was wrong so rollback.All new hires are taught that "begin transaction" is part of a delete statement per the CYA rule. Also any time 'begin transaction' is used, you are not allowed to leave for lunch until after commit or rollback. (that was a hard-won lesson)Is there ever a case where row-level failures are acceptable and a batch should be committed regardless? I understand ETL processes might have some reason to not insert already-existing rows, but shouldn't that be handled explicitly rather than ignoring key/constraint failures? How would you explain to management or a coworker why this is a Bad Thing?Wed, 23 May 2012 07:29:34 GMTMike Dougherty-384281http://www.sqlservercentral.com/Forums/Topic1304651-1222-1.aspx[quote][b]Hugo Kornelis (5/23/2012)[/b][hr]Only when you declare an explicit transaction [b]AND[/b] you don't want to add explicit error handling [b]AND[/b] you are fine with the default error handling with XACT_ABORT set to ON. There are lots of situations where you would not want that.[/quote]Most situations I'd have thought. Wouldn't youy normally want unexpected errors of this sort to be logged somewhere? Using XACT_ABORT won't do this, but explicit error handling will if you remember to code it for course).Wed, 23 May 2012 07:13:12 GMTToreadorhttp://www.sqlservercentral.com/Forums/Topic1304651-1222-1.aspx[quote][b]mtassin (5/23/2012)[/b][hr]But this would also work as though xact_abort is on :)[code]BEGIN trybegin TRANSACTIONINSERT qotd2(col1,col2,col3) VALUES (1,'x','some')INSERT qotd2(col1,col2,col3) VALUES (1,'Y','thing')INSERT qotd2(col1,col2,col3) VALUES (2,'Z','or other')COMMITEND TRYBEGIN CATCHROLLBACKEND catchSELECT * FROM qotd2[/code][/quote]No, it would not. With XACT_ABORT set to ON, the final SELECT statement would never be executed. XACT_ABORT causes the batch to be terminated on an error.Wed, 23 May 2012 07:11:44 GMTHugo Kornelishttp://www.sqlservercentral.com/Forums/Topic1304651-1222-1.aspx[quote][b]roger.plowman (5/23/2012)[/b][hr]Having the default setting to OFF basically break transactions and is a *bug* in SQL Server.Explicit transactions should be exactly that, explicit. This stupidity is exactly the kind of thing that will cause nearly untraceable bugs in code. What were they thinking?[/quote]They were probably thinking that explicit transactions usually come with explicit error handling. For instance using the TRY CATCH pattern, as shown by mtassin.[quote][b]Daniel Bowlin (5/23/2012)[/b][hr]So the take away here is that whenever you need to declare an explicit transaction you should include SET XACT_ABORT ON before proceeding.[/quote]Hmm, no. Only when you declare an explicit transaction [b]AND[/b] you don't want to add explicit error handling [b]AND[/b] you are fine with the default error handling with XACT_ABORT set to ON. There are lots of situations where you would not want that.Wed, 23 May 2012 07:09:20 GMTHugo Kornelishttp://www.sqlservercentral.com/Forums/Topic1304651-1222-1.aspx[quote][b]Daniel Bowlin (5/23/2012)[/b][hr]I agree with some of the other posts that express frustration that this is the default setting that seems rather contrary to an explicit transaction. So the take away here is that whenever you need to declare an explicit transaction you should include SET XACT_ABORT ON before proceeding.I have to remember that one, thanks.[/quote]So just remember the Begin transaction statement is spelled SET XACT_ABORT ONBEGIN TRANSACTION:hehe:Wed, 23 May 2012 07:03:52 GMTroger.plowmanhttp://www.sqlservercentral.com/Forums/Topic1304651-1222-1.aspx[quote][b]Neil Thomas (5/23/2012)[/b][hr]So that means that when xact_abort is set to off thenBEGIN TRANSACTION ...COMMIT TRANSACTIONdoes not do as expected.What's the default setting on a fresh install of SQL?[/quote]But this would also work as though xact_abort is on :)[code]BEGIN trybegin TRANSACTIONINSERT qotd2(col1,col2,col3) VALUES (1,'x','some')INSERT qotd2(col1,col2,col3) VALUES (1,'Y','thing')INSERT qotd2(col1,col2,col3) VALUES (2,'Z','or other')COMMITEND TRYBEGIN CATCHROLLBACKEND catchSELECT * FROM qotd2[/code]Wed, 23 May 2012 06:54:51 GMTmtassinhttp://www.sqlservercentral.com/Forums/Topic1304651-1222-1.aspxI agree with some of the other posts that express frustration that this is the default setting that seems rather contrary to an explicit transaction. So the take away here is that whenever you need to declare an explicit transaction you should include SET XACT_ABORT ON before proceeding.I have to remember that one, thanks.Wed, 23 May 2012 06:51:44 GMTDaniel Bowlinhttp://www.sqlservercentral.com/Forums/Topic1304651-1222-1.aspxHaving the default setting to OFF basically break transactions and is a *bug* in SQL Server.Explicit transactions should be exactly that, explicit. This stupidity is exactly the kind of thing that will cause nearly untraceable bugs in code. What were they thinking?However, kudos for pointing this out, at least now it won't catch me... Member of SPCP -- Society for the Prevention of Cruelty to ProgrammersWed, 23 May 2012 06:43:53 GMTroger.plowman