none
autocommit

    Question

  • Hi,

    I remember for the previous version, "autocommit" is defaulted to be on. How about sql server 2008 management studio? Where can we change its default?


    Many Thanks & Best Regards, HuaMin Chen
    Thursday, April 7, 2011 4:12 AM

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 HuaMin Chen Thursday, April 7, 2011 6:16 AM
    Thursday, April 7, 2011 4:23 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 HuaMin Chen Thursday, April 7, 2011 6:15 AM
    Thursday, April 7, 2011 4:27 AM
  • If you SET IMPLICIT_TRANSACTIONS on then you need manually to rollback and commit.

    by default the IMPLICIT_TRANSACTIONS  is set to off and commit is automatically done.

    • Marked as answer by HuaMin Chen Thursday, April 7, 2011 6:15 AM
    Thursday, April 7, 2011 5:37 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 HuaMin Chen Thursday, April 7, 2011 6:16 AM
    Thursday, April 7, 2011 4:23 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 HuaMin Chen Thursday, April 7, 2011 6:15 AM
    Thursday, April 7, 2011 4:27 AM
  • Many thanks all. Manub, does "SET IMPLICIT_TRANSACTIONS on" implies "autocommit" is on?
    Many Thanks & Best Regards, HuaMin Chen
    Thursday, April 7, 2011 4:54 AM
  • If you SET IMPLICIT_TRANSACTIONS on then you need manually to rollback and commit.

    by default the IMPLICIT_TRANSACTIONS  is set to off and commit is automatically done.

    • Marked as answer by HuaMin Chen Thursday, April 7, 2011 6:15 AM
    Thursday, April 7, 2011 5:37 AM