Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
add comment

1 Answer

up vote 2 down vote accepted

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.

share|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
show 4 more comments

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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