Post reply

Row count in temp table

  • June 16, 2011 at 12:22 pm

    #1339963

    Nested transactions

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

    Jayanth Kurup[/url]

  • June 16, 2011 at 12:30 pm

    #1339975

    GilaMonster (6/16/2011)

    ...Not what I was thinking about at all. For savepoints, the name of the transaction is still meaningless, the name of the savepoint is what's important.

    Agreed. There is an important distinction in terminology that I missed that is not present in the application of the technique and I used them interchangeably.

    Transaction/Savepoint names are a huge source of confusion. The syntax allows what follows a ROLLBACK TRAN to be either a savepoint_name or a transaction_name...and it can't be determined based only on the rollback statement alone.

    ROLLBACK { TRAN | TRANSACTION }

    [ transaction_name | @tran_name_variable

    | savepoint_name | @savepoint_variable ]

    [ ; ]

    Providing a "transaction name" in the BEGIN TRAN statement does not have an effect on the transaction boundary in a SAVE TRAN scenario as you demoed. I'll have to adjust my terminology.

    There are no special teachers of virtue, because virtue is taught by the whole community.
    --Plato

  • June 17, 2011 at 6:48 am

    #1340367

    Sachin4all (6/13/2011)

    Begin Transaction Tran7

    insert into #t (...)

    SELECT ..

    FROM #hs t

    INNER JOIN table1 on

    If @@ERROR <> 0

    Begin

    Rollback Transaction Tran7

    INSERT INTO LogTable Values (7,@@ROWCOUNT,'F',@Startdate,getdate())

    End

    Else

    Begin

    Commit transaction Tran7

    INSERT INTO LogTable Values (7,@@ROWCOUNT,'S',@Startdate,getdate())

    end

    Can you tell me how to rollback the transaction if it fails?

    This is a VERY common misunderstanding. You MUST store BOTH variables IMMEDIATELY after the DML, thusly:

    DO SOME DML

    SELECT @rowcount = @@ROWCOUNT, @err = @@ERROR

    IF...

    Best,
    Kevin G. Boles
    SQL Server Consultant
    SQL MVP 2007-2012
    TheSQLGuru on googles mail service

Viewing 3 posts - 16 through 17 (of 17 total)

You must be logged in to reply to this topic. Login to reply