August 1, 2005 at 9:19 am
Hi all,
I want to know,how can I enable autocommit in sql server? I want to set it in system level not at session level.
-Thanks
August 1, 2005 at 9:48 am
Hi Ach,
From BOL
Autocommit mode is the default transaction management mode of Microsoft® SQL Server™. 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 SQL Server connection 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 SQL Server connection 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, SQL Server returns to autocommit mode.
but if u have set the
SET IMPLICIT_TRANSACTIONS ON then u have to define commit or rollback .
Hope this help u
from
Killer
August 2, 2005 at 2:22 am
SET IMPLICIT_TRANSACTIONS [ON | OFF]
will set transactions only for the current connection.
To change the server default for all new connections:
In Enterprise Manager: Right click server -> properties -> Connections tab -> Tick "Implicit Transactions" in the Connections / Default Connection Options area.
In Query Analyzer: exec sp_configure 'user options'
will show you the current bit-value options that are set. Setting it to '2' turns on Implicit_transactions for all connections (and all other options off). Checkout "user option Options" on BOL for more info.
Julian Kuiters
juliankuiters.id.au
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply