What is XACT_ABORT in terms of implicit or explicit transactions?

ByDr. SubraMANI Paramasivam

What is XACT_ABORT in terms of implicit or explicit transactions?

When it comes to data modification statements for OLE DB systems, the XACT_ABORT option with ON and OFF functions helps the Developer/Administrator to decide based on the requirements from the business.

For example lets take a stored procedure that will complete fully, only if the UPDATE statements are completed. In this case the XACT_ABORT option should be set to ON like below.

SET XACT_ABORT ON;

GO

BEGIN TRANSACTION

By setting this XACT_ABORT option to ON, it does not affect trapping of any errors using TRY-CATCH, but it helps in a scenario where the commit or rollback was successful or not.

 

 

About the Author

Dr. SubraMANI Paramasivam administrator

Dr.SubraMANI Paramasivam is a Data Platform MVP, Power BI Group Leader, International Speaker, Microsoft Certified Trainer and Principal Solutions Architect at DAGEOP Ltd, having 20+ years of DB, BI & Analytics experience. Dr.SubraMANI Paramasivam is a frequent speaker in SQLBITS, PASS Summit, PASS SQL Saturdays, SQLRelay, Data Platform Summit, MCT Summit, SQLServerGeeks Summit, INSIDESQL UK, Data Awareness Programmes, Data Day Events. He likes to enlighten the education & data and started his own journey with FREE Data Awareness Programme (www.dataap.org), in remote areas who are in need. Mani speaks a lot about Visualization, Data Science, R Analytics, Azure concepts, Performance Tuning, DB & SQL Server Architecture, DBA, Partitioning, Resource Governor, Disaster Recovery, High Availability, Development, T-SQL Programming and Microsoft certifications.

Comments Are Closed!!!