Answered by:
autocommit

Question
-
Answers
-
Go to SSMS, Menu, Tools, Options, Query Execution, SQL Server, ANSI.
Here check the option on SET IMPLICIT_TRANSACTIONS
~Manoj (manub22@gmail.com)
http://sqlwithmanoj.wordpress.com
MCCA 2011- Marked as answer by Ai Guo Thursday, April 7, 2011 6:16 AM
-
Autocommit defaults to be on for SQL 2008. As SQL 2008 BOL says (see http://msdn.microsoft.com/en-us/library/ms187878%28v=SQL.100%29.aspx)
"Autocommit mode is the default transaction management mode of the SQL Server Database Engine. Every Transact-SQL statement is committed or rolled back when it completes. If a statement completes successfully, it is committed; if it encounters any error, it is rolled back. A connection to an instance of the Database Engine operates in autocommit mode whenever this default mode has not been overridden by either explicit or implicit transactions. Autocommit mode is also the default mode for ADO, OLE DB, ODBC, and DB-Library.
A connection to an instance of the Database Engine operates in autocommit mode until a BEGIN TRANSACTION statement starts an explicit transaction, or implicit transaction mode is set on. When the explicit transaction is committed or rolled back, or when implicit transaction mode is turned off, the connection returns to autocommit mode."
There is a user option named IMPLICIT_TRANSACTIONS which will set implicit transactions ON when the connection is made by a user that has that option. But it only works if you are using DBLIB which is an old deprecated connection method that has many restrictions and should only be used for legacy applications. That user option is ignored if you are using ODBC or OLEDB connections.
Tom
- Marked as answer by Ai Guo Thursday, April 7, 2011 6:15 AM
-
All replies
-
Go to SSMS, Menu, Tools, Options, Query Execution, SQL Server, ANSI.
Here check the option on SET IMPLICIT_TRANSACTIONS
~Manoj (manub22@gmail.com)
http://sqlwithmanoj.wordpress.com
MCCA 2011- Marked as answer by Ai Guo Thursday, April 7, 2011 6:16 AM
-
Autocommit defaults to be on for SQL 2008. As SQL 2008 BOL says (see http://msdn.microsoft.com/en-us/library/ms187878%28v=SQL.100%29.aspx)
"Autocommit mode is the default transaction management mode of the SQL Server Database Engine. Every Transact-SQL statement is committed or rolled back when it completes. If a statement completes successfully, it is committed; if it encounters any error, it is rolled back. A connection to an instance of the Database Engine operates in autocommit mode whenever this default mode has not been overridden by either explicit or implicit transactions. Autocommit mode is also the default mode for ADO, OLE DB, ODBC, and DB-Library.
A connection to an instance of the Database Engine operates in autocommit mode until a BEGIN TRANSACTION statement starts an explicit transaction, or implicit transaction mode is set on. When the explicit transaction is committed or rolled back, or when implicit transaction mode is turned off, the connection returns to autocommit mode."
There is a user option named IMPLICIT_TRANSACTIONS which will set implicit transactions ON when the connection is made by a user that has that option. But it only works if you are using DBLIB which is an old deprecated connection method that has many restrictions and should only be used for legacy applications. That user option is ignored if you are using ODBC or OLEDB connections.
Tom
- Marked as answer by Ai Guo Thursday, April 7, 2011 6:15 AM
-
-