none
committing transactions in SQL Server Management Studio Express

    Question

  • I have been using SQL Navigator (an Oracle product) and have just started using SQL Server Management Studio Express.  In SQL Navigator, when I update, delete or insert data into a table I have to do an explicit commit.  I like this feature because I can see how many rows are affected before the change is complete.  In SQL Server Management Studio Express is there a way that I can rollback a transaction? 

     

    Does anyone know of a good documentation for someone transitioning from supporting Oracle DBs to SQL Server DBs?

     

    Thank you.

    Robin

    Thursday, January 24, 2008 7:40 PM

Answers


  • In SQL Server, transactions are implicit by design. You can simulate the same behaviour you are comfortable with oracle by placing a begin tran command at the top of your insert/update/delete query, run your query, verify the data and upon verification you can run commit tran by itself to complete the transaction.


    Thursday, January 24, 2008 8:16 PM

All replies


  • In SQL Server, transactions are implicit by design. You can simulate the same behaviour you are comfortable with oracle by placing a begin tran command at the top of your insert/update/delete query, run your query, verify the data and upon verification you can run commit tran by itself to complete the transaction.


    Thursday, January 24, 2008 8:16 PM
  • I don't think SQL Server Management Studio does this...You may use query analyser with BEGIN TRAN /COMMIT/RollBack Tran

     

    Madhu

    Friday, January 25, 2008 3:44 AM
    Moderator
  • --start you sctipt by

    SET IMPLICIT_TRANSACTIONS ON

    go

     

     

    Monday, April 21, 2008 2:25 PM
  • As already posted you can specify prior to the transaction BEGIN TRAN.  This will require you to COMMIT TRAN or ROLLBACK TRAN.  This is useful, but comes at a price of locks placed on the specific level of the transaction.  As also stated you can specify SET IMPLICIT_TRANSACTIONS ON which will also accomplish the same task.

     

    Code Snippet

    SET IMPLICIT_TRANSACTIONS ON

    UPDATE [Employees]

    SET [LastName] = 'Dye'

    ROLLBACK TRAN

     

    OR

     

    BEGIN TRAN

    UPDATE [Employees]

    SET [LastName] = 'Dye'

    ROLLBACK TRAN

     

     

    The following links will provide you information on this.

    http://msdn2.microsoft.com/en-us/library/ms187807.aspx

    http://msdn2.microsoft.com/en-us/library/ms188929.aspx

     

    The below link is for SQL Server 2005 technet virtual labs.  This provides you online training on a virtual sql server instance which is free.

    http://technet.microsoft.com/en-us/bb499681.aspx

     

    Monday, April 21, 2008 5:40 PM
    Moderator