Disable autocommit for SQL Server connection in Oracle SQL Developer

Question

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.

Answer 1

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.

• Jon Seigel: @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)

Answer 2

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

• András Váczi: The OP clearly says: 'BEGIN TRANSACTION is not a solution for me.'
• LiborStefek: 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: 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.