Thursday, September 25, 2014

How to set Auto Commit OFF in SQL Server or Disable Auto Commit in SQL Server

Developers moving from Oracle to SQL Server often face issues with commit transactions. Because Oracle doesn't do AUTO COMMIT but SQL Server does and here developer faces the issues. Sometimes during testing we delete rows which we shouldn't be and can't ROLLBACK as these transactions in SQL Server are auto committed by default.


So the question is can we set this off ? Yes, we can and here is how you can do that.


There are two ways to achieve this.

You can  disable AUTO COMMIT at database level as shown below.

Option 1
  1. Open SQL Server Management Studio
  2. Right Click on Server  and Select  Properties
  3. You will now be in Server Properties
  4. Go to Connections page and check the options as shown below.



Option 2

  1. Connect to Database Instance where you want to set Auto Commit off.
  2. Go to SQLQuery window
  3. Execute below command

SET IMPLICIT_TRANSACTIONS ON;

Once this is executed , you can use ROLLBACK to undo the changes or COMMIT to save the changes.

No comments:

Post a Comment