How do you set autocommit in an SQL Server session?

by

You can turn autocommit ON by setting implicit_transactions OFF:

SET IMPLICIT_TRANSACTIONS OFF

When the setting is ON, it returns to implicit transaction mode. In implicit transaction mode, every change you make starts a transactions which you have to commit manually.

Maybe an example is clearer. This will write a change to the database:

SET IMPLICIT_TRANSACTIONS ON
UPDATE MyTable SET MyField = 1 WHERE MyId = 1
COMMIT TRANSACTION

This will not write a change to the database:

SET IMPLICIT_TRANSACTIONS ON
UPDATE MyTable SET MyField = 1 WHERE MyId = 1
ROLLBACK TRANSACTION

The following example will update a row, and then complain that there’s no transaction to commit:

SET IMPLICIT_TRANSACTIONS OFF
UPDATE MyTable SET MyField = 1 WHERE MyId = 1
ROLLBACK TRANSACTION

Like Mitch Wheat said, autocommit is the default for Sql Server 2000 and up.

More Related Contents:

  1. Max Nbr of Months W/ No Activity in Past 12 Months
  2. Export from SQL Server to Excel
  3. SQL server query to output the data shown below
  4. hide/remove header and footer
  5. What is the difference between Integrated Security = True and Integrated Security = SSPI?
  6. How to use a variable for the database name in T-SQL?
  7. How do you view ALL text from an ntext or nvarchar(max) in SSMS?
  8. SQL Server: SELECT only the rows with MAX(DATE)
  9. Create a view with ORDER BY clause
  10. What does ‘COLLATE SQL_Latin1_General_CP1_CI_AS’ do?
  11. INSERT INTO vs SELECT INTO
  12. Possible to store value of one select column and use it for the next one?
  13. LEN function not including trailing spaces in SQL Server
  14. How to view transaction logs in SQL Server 2008 [closed]
  15. Scheduled run of stored procedure on SQL server
  16. SQL Server ORDER BY date and nulls last
  17. Best way to implement an audit trail in SQL Server?
  18. How can I change from SQL Server Windows mode to mixed mode (SQL Server 2008)?
  19. How can I Insert data into SQL Server using VBNet
  20. How do you determine what SQL Tables have an identity column programmatically
  21. Ampersand (&) operator in a SQL Server WHERE Clause
  22. how to write number to word function in sql server [closed]
  23. Milliseconds wrong when converting from XML to SQL Server datetime
  24. Using GROUP BY with FIRST_VALUE and LAST_VALUE
  25. Is there an “IF EXISTS” test for a linked server?
  26. How do you deal with blank spaces in column names in SQL Server?
  27. Unique identifier (guid) as primary key in database design
  28. How to extract this specific substring in SQL Server?
  29. SQL Server stored procedure return code oddity
  30. ODBC Call Failed with stored procedure – Pass through query

Leave a Comment