How do you set autocommit in an SQL Server session?
This question is related to
sql-server
session
autocommit
Autocommit is SQL Server's default transaction management mode. (SQL 2000 onwards)
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.
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.
Source: Stackoverflow.com