How do you set autocommit in an SQL Server session

Question

User · Answer

With SQLServer 2005 Express  what I found was that even with autocommit off  insertions into a Db table were committed without my actually issuing a commit command from the Management Studio session  The only difference was  when autocommit was off  I could roll back all the insertions  with  autocommit on  I could not    Actually  I was wrong  With autocommit mode off  I see the changes only in the QA  Query Analyzer  window from which the commands were issued  If I popped a new QA  Query Analyzer  window  I do not see the changes made by the first window  session   i e  they are NOT committed  I had to issue explicit commit or rollback commands to make changes visible to other sessions QA windows  -- my bad  Things are working correctly

User · Answer

Autocommit is SQL Server s default transaction management mode   SQL 2000 onwards   Ref  Autocommit Transactions

User · Answer

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

User · Answer

I wanted a more permanent and quicker way  Because I tend to forget to add extra lines before writing my actual Update Insert queries   I did it by checking SET IMPLICIT TRANSACTIONS check-box from Options  To navigate to Options Select Tools Options Query Execution SQL Server ANSI in your Microsoft SQL Server Management Studio   Just make sure to execute commit or rollback after you are done executing your queries  Otherwise  the table you would have run the query will be locked for others

[sql-server] How do you set autocommit in an SQL Server session?

Examples related to sql-server

Examples related to session

Examples related to autocommit