I have done some update to my record through SQL Server Manager.
As Update statement is not having explicit commit, I am trying to write it manually.
Update mytable
set status=0;
Commit;
I am getting message as Commit has no begin transaction
Asked
Active
Viewed 1.8e+01k times
27
-
1You should begin a Transaction before committing. – Nadeem_MK Sep 26 '13 at 11:12
-
2You should open a transaction. Inside it you should perform update then commit changes. – lexeme Sep 26 '13 at 11:12
-
See how it works here: http://www.codeproject.com/Articles/10223/Using-Transactions-in-ADO-NET – lexeme Sep 26 '13 at 11:13
2 Answers
59
The SQL Server Management Studio has implicit commit turned on, so all statements that are executed are implicitly commited.
This might be a scary thing if you come from an Oracle background where the default is to not have commands commited automatically, but it's not that much of a problem.
If you still want to use ad-hoc transactions, you can always execute
BEGIN TRANSACTION
within SSMS, and than the system waits for you to commit the data.
If you want to replicate the Oracle behaviour, and start an implicit transaction, whenever some DML/DDL is issued, you can set the SET IMPLICIT_TRANSACTIONS checkbox in
Tools -> Options -> Query Execution -> SQL Server -> ANSI
Mauricio Gracia Gutierrez
- 10,288
- 6
- 68
- 99
SWeko
- 30,434
- 10
- 71
- 106
-
2Hi @SWeko. very helpful, thx. Am i right to assume, that checkbox unchecked means that transactions are commited implicitly? Thats my (default) situation. Naming of that checkbox is weird, then. btw, related issue here: http://stackoverflow.com/questions/17576649/safely-delete-rows-in-sqlserver-so-you-can-rollback-a-commit – Der U Sep 26 '13 at 11:31
-
2If the checkbox is checked, then a `SELECT` statement, implicitly starts a transaction, that will need to be explicitly commited. – SWeko Sep 26 '13 at 11:46
6
Sql server unlike oracle does not need commits unless you are using transactions.
Immediatly after your update statement the table will be commited, don't use the commit command in this scenario.
Kristof
- 3,267
- 1
- 20
- 30