I am using Oracle's SQL Developer to execute queries against my SQL Server 2008 database as I simply can't work with SSMS as long as it doesn't support Ctrl + Return to execute statements...

Anyway, statements in SQL Server seem to be autocommit even when using Oracle SQL Developer. Is it possible to change this on a permanent basis? BEGIN TRANSACTION is not a solution for me.


SQL Server uses autocommit mode by default. This cannot be changed permanently.

There are two ways implicit transactions (non-autocommit) can be turned on:

  1. At the server level such that new sessions use it by default, using sp_configure 'user options' -- this may or may not work depending on how SQL Developer was implemented.

  2. For a session, using SET IMPLICIT_TRANSACTIONS ON.

| improve this answer | |
  • Unfortunately, I haven't found a way to use this option in SQL Developer. When executing it, I get "SET IMPLICIT_TRANSACTIONS ON" – Daniel Hilgarth Jul 24 '12 at 21:28
  • @Daniel: See my edit. If neither of those work, I don't know what to suggest... if SQL Developer isn't passing that statement on to SQL Server, you're out of luck (use a different development tool). – Jon Seigel Jul 24 '12 at 21:44
  • The user options can even be configured via a dialog, as described here. "implicit transactions" is the first entry in the list. But it doesn't seem to make an difference, not in SQL Developer and not in SSMS... :( – Daniel Hilgarth Jul 24 '12 at 22:03
  • @Daniel: Yes, the GUI uses sp_configure under the hood. As the linked document says, that option only works in very specific cases. – Jon Seigel Jul 24 '12 at 22:20
  • 1
    @Daniel: "For dblib network library connections, controls whether a transaction is started implicitly when a statement is executed. The IMPLICIT_TRANSACTIONS setting has no effect on ODBC or OLEDB connections." (emphasis mine) – Jon Seigel Jul 25 '12 at 12:58

In SQL Developer you can try this technique:

/*sqldev:stmt*/begin transaction;
update ...

See: http://stackoverflow.com/questions/17826779/disable-autocommit-in-sql-developer-when-using-mysql

| improve this answer | |
  • 1
    The OP clearly says: 'BEGIN TRANSACTION is not a solution for me.' – dezso Nov 3 '15 at 16:01
  • Well, actually, SQL Developer is blocking everything starting with BEGIN. So, BEGIN TRANSACTION itself is not a solution for anybody. The magic comment does the trick. I was looking for the same in SQL Developer as the OP; I think this solution sould be mentioned here. It wasn't straiforward to find it. – LiborStefek Nov 3 '15 at 18:17
  • SQL Developer is written in Java and typicaly uses JDBC Drivers for connection to databases. Answer given by Jon is not working for Daniel nor for me. – LiborStefek Nov 3 '15 at 18:33

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.