Post reply

Begin Transaction

  • September 5, 2006 at 11:04 am

    #113499

    I am trying to execute a stored procedure within a VB script loop.  The stored procedure has a begin transaction along with ROllback and Commit work depending on success or failure.  For some reason, after the first loop, it seems like the vb program is stuck.  I am assuming the first transaction has not released the lock on the row for some reason or the first transaction has a table lock of some sort (I hope not)...  Any suggestions???

  • September 5, 2006 at 11:21 am

    #658608

    did u try running the the sp with the first parameter manually and then run the send call manually and see if you have the appropriate "Commit" or "Rollback Tran"?

    That should give an indication of any issues with the sp. Also if you have transactions with the sp you need to check if the VB script is also called within a Transaction (Has happened with DTS)

    Thanks

    Sreejith

  • September 5, 2006 at 11:27 am

    #658611

    Streejith,

    Here is the stored procedure.  It is pretty straightforward.  I am going to try using profiler to see if there any lock...

    SET XACT_ABORT ON

    SET NOCOUNT ON

    BEGIN TRANSACTION

    UPDATE MYTABLE SET TRNSTAT='Y'

    WHERE MYSEQUENCE=@MY_SEQUENCE

    IF @@ERROR <> 0 THEN

    ROLLBACK TRANS

    ELSE

    COMMIT WORK

    END

  • September 5, 2006 at 11:59 am

    #658624

    when SET XACT_ABORT ON statement transaction is terminated and rolled back implicitly

    If any error occurs in Update statement, It wont go to the IF condition after that because of SET XACT_ABORT ON

    moreover the code you pasted doesn't parsed to be correct sql

    If possible, paste exact sql

Viewing 4 posts - 1 through 3 (of 3 total)

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