I'm used to work with tools like Toad or PL/Sql Developer for Oracle and every SQL statement I run on the query window runs automatically under a trasaction scope. So, if my SQL statement is not correct, or even if I miss something on the SQL script I can rollback only by clicking on a button at the UI. If everything worked as expected, I can click on another button for commiting the current transaction. On those tools I mentioned, I don't need to write SQL statements for begin, commit or rollback a transaction. Every SQL statement has an implicit transaction associated with.
Is there a way to achieve the same functionality on SQL Server Management Studio 2008?
The answer is quick. Simply uncheck the option SET IMPLICIT_TRASLATIONS. To do it follow next path from the menu: Tools> Options> Execution of the query> SQL Server> ANSI, then Uncheck Option SET IMPLICIT_TRANSACTIONS.
The auto-commit transaction mode is the default transaction mode of the SQL Server. In this mode, each SQL statement is evaluated as a transaction by the storage engine.
@@TRANCOUNT (Transact-SQL)Returns the number of BEGIN TRANSACTION statements that have occurred on the current connection.
Example of COMMIT Transaction The following steps illustrate to create a transaction: Start the transaction using the BEGIN TRANSACTION command. Write the SQL statements and divide them based on our needs. Use the COMMIT statement to complete the transaction and save the changes permanently.
You'd have to set "Implict Transactions" in tools..options..query execution
To enable COMMIT or ROLLBACK, I can only think of using the CTRL+number shortcuts
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With