SQL Server Performance

how to set AUTOCOMMIT ON in sqlserver 2005

Discussion in 'SQL Server 2005 General DBA Questions' started by narasareddy, Aug 26, 2008.

  1. narasareddy New Member

    Hi All,
    I am new to SQL Server 2005. i am inserting one row through front-end Application, and i am not commit that time.
    My problem is How to COMMIT the DATABSE
    Please help Me,
    Thanks & Regards
    Narasareddy

    narasareddy, Aug 26, 2008
    #2

  2. moh_hassan20 New Member

    what is your front end application.?

    moh_hassan20, Aug 27, 2008
    #3

  3. narasareddy New Member

    Centura( Gupta Technologies ) and back-end is sql Server 2005
    narasareddy, Aug 27, 2008
    #4

  4. atulmar New Member

    It is by default on.
    SET IMPLICIT_TRANSACTIONS ON
    This is setting for transactions, until unless you are handling transactions explicitely. Like use of SQLTransaction claess in .Net Apps.
    atulmar, Aug 27, 2008
    #5

  5. moh_hassan20 New Member

    i cann't guess if Centura application use odbc or Native Client OLE DB Provider for SQL Server.
    As i know , The SQL Server Native Client OLE DB Provider for SQL Server and the SQL Server Native Client ODBC driver automatically set IMPLICIT_TRANSACTIONS to OFF when connecting.

    IMPLICIT_TRANSACTIONS OFF, set the connection to autocommit mode. In that case of autocommit mode, all individual statements are committed automatic if they complete successfully.
    review:
    http://msdn.microsoft.com/en-us/library/ms187807.aspx
    http://support.microsoft.com/kb/198024
    review the docs of the application to know setting needed for commiting trans safely.
    moh_hassan20, Aug 29, 2008
    #6

  6. atulmar New Member

    MSDN says
    When SET ANSI_DEFAULTS is ON, SET IMPLICIT_TRANSACTIONS is ON.select
    ansi_defaults from sys.dm_exec_sessions
    You can check this one for your session, which is opened by application.
    atulmar, Aug 29, 2008
    #7

  7. satya Moderator

    Referring what is documented:
    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.
    satya, Aug 31, 2008
    #8

Share This Page