Best Way to Work with Transactions in Ms SQL Server Management Studio

Best way to work with transactions in MS SQL Server Management Studio

The easisest thing to do is to wrap your code in a transaction, and then execute each batch of T-SQL code line by line.

For example,

Begin Transaction

         -Do some T-SQL queries here.

Rollback transaction -- OR commit transaction

If you want to incorporate error handling you can do so by using a TRY...CATCH BLOCK. Should an error occur you can then rollback the tranasction within the catch block.

For example:

USE AdventureWorks;
GO
BEGIN TRANSACTION;

BEGIN TRY
    -- Generate a constraint violation error.
    DELETE FROM Production.Product
    WHERE ProductID = 980;
END TRY
BEGIN CATCH
    SELECT 
        ERROR_NUMBER() AS ErrorNumber
        ,ERROR_SEVERITY() AS ErrorSeverity
        ,ERROR_STATE() AS ErrorState
        ,ERROR_PROCEDURE() AS ErrorProcedure
        ,ERROR_LINE() AS ErrorLine
        ,ERROR_MESSAGE() AS ErrorMessage;

    IF @@TRANCOUNT > 0
        ROLLBACK TRANSACTION;
END CATCH;

IF @@TRANCOUNT > 0
    COMMIT TRANSACTION;
GO

See the following link for more details.

http://msdn.microsoft.com/en-us/library/ms175976.aspx

Hope this helps but please let me know if you need more details.

Correct use of transactions in SQL Server

Add a try/catch block, if the transaction succeeds it will commit the changes, if the transaction fails the transaction is rolled back:

BEGIN TRANSACTION [Tran1]

  BEGIN TRY

      INSERT INTO [Test].[dbo].[T1] ([Title], [AVG])
      VALUES ('Tidd130', 130), ('Tidd230', 230)

      UPDATE [Test].[dbo].[T1]
      SET [Title] = N'az2' ,[AVG] = 1
      WHERE [dbo].[T1].[Title] = N'az'

      COMMIT TRANSACTION [Tran1]

  END TRY

  BEGIN CATCH

      ROLLBACK TRANSACTION [Tran1]

  END CATCH  

How to enable automatic transaction scoping on SQL Server Management Studio?

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

SQL Management Studio - avoiding automatic commit

You can set implicit transactions in SQL-Server Managemenet Studio. Therefore you have to change the default option from off to on here:

• Tools
• Options
• Query Execution
• SQL Server
• ANSI
• SET IMPLICIT_TRANSACTIONS

Here is an image:

SQL Server Management Studio warns about uncommitted transactions even on selects

According to documentation Implicit Transactions

In implicit transaction mode your SELECT opens a transaction as well and it cannot be changed. That is by design.

So you'll continue to get warnings until you commit/rollback your SELECT statements

Can we use 'GO' multiple times in SQL Transaction?

You are mixing concepts. GO is not a Transact-SQL concept, not part of the language, and not understood by SQL Server. GO is the tools batch delimiter. sqlcmd.exe and SSMS both are using, by default, GO as the batch delimiter. The batch delimiter is used to identify the individual batches inside the SQL source file. The client tool sends to the server one batch at a time (of course, omitting the delimiter).

Transactions can span batches. TRY/CATCH blocks cannot. CREATE/ALTER statements must be the only statement in a batch (comments are not statements, and statements contained in a function procedure body are,well, contained).

Something similar to what you want to do can be achieved by starting a transaction and abortign the execution on first error (-b at sqlcmd.exe start, or use :on error exit in SSMS).

But doing DDL inside long transactions is not going to work. Specially if you plan to mix it with DML. Most corruptions I had to investigate come from this combination (Xact, DDL + DML, rollback). I strongly recommend against it.

The sole way to deploy schema updates safely is to take a backup, deploy, restore from backup if something goes wrong.

Note that what Dan recommends (dynamic SQL) works because sp_executesql starts a new, inner, batch. This batch will satisfy the CREATE/ALTER restrictions.

---

Related Topics