3

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.

Improve this question

2 Answers 2

Reset to default
2

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
9
  • 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, 2012 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, 2012 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, 2012 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, 2012 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, 2012 at 12:58
-6

In SQL Developer you can try this technique:

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

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

Improve this answer
3
  • 1
    The OP clearly says: 'BEGIN TRANSACTION is not a solution for me.'
    – dezso
    Nov 3, 2015 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, 2015 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, 2015 at 18:33

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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