THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Tibor Karaszi

July SSMS 2016 changes transaction semantics

This can be a very nasty one if you're not observant. The July update of SSMS now has the option to change XACT_ABORT setting for a query window using

  • Tools
  • Options
  • Query Execution
  • SQL Server
  • Advanced
  • SET XACT_ABORT
Now, exposing one more SET option can't be bad, right? But Erland Sommarskog found out that this is checked by default. Now, using XACT_ABORT might now be a bad thing, au contraire. See this one of Erland's series of error handle articles.
 
The problem is that it is a change. Quite of a sudden, your transaction semantics isn't the same, and if you execute that script from somewhere else, or even an older SSMS, it doesn't behave the same. For instance, if you don't use TRY CATCH (which you should be using), then the batch will now stop and rollback on most errors, instead of allowing you to do the old eighties style IF @@ERROR <> 0 dance. 
 
Personally, I SET my set commands instead of checking a checkbox in SSMS. But I don't SET all set options there are, just the ones I want to change from default. I hope that this one can be reverted so it isn't checked by default.
Published Tuesday, July 05, 2016 10:45 AM by TiborKaraszi
Filed under: ,

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

Pedro Lopes said:

Hi, change log has been edited to account for the change: https://msdn.microsoft.com/en-us/library/mt238290.aspx

Thanks!

July 7, 2016 11:56 AM

Leave a Comment

(required) 
(required) 
Submit

This Blog

Syndication

Privacy Statement