Results 1 to 6 of 6
  1. #1
    Join Date
    May 2002
    Posts
    395

    Unanswered: rollback doesn't work

    I'm having trouble getting the rollback statement to work in my stored procedure. Could anyone see what the problem is?

    Thanks!!

    Alter Procedure "spzUpdate_shipping_sched"
    as
    declare @ErrorCode int, @intRowCount int;

    Select @ErrorCode = @@Error, @intRowCount = @@RowCount

    Begin

    --Select @ErrorCode = 1

    insert into tblShipment_history (work_ord_num, ...)
    SELECT work_ord_num,...
    FROM tblShipping_sched
    WHERE shipped_qty IS NOT NULL
    ORDER BY shipped_qty DESC;

    EXECUTE spClear_temp_shipping_feilds
    --Select @ErrorCode = @@Error, @intRowCount = @@RowCount

    select @ErrorCode = 1

    --this if statement works
    If @ErrorCode = 0 and @intRowCount > 0

    DELETE FROM tblShipping_sched WHERE shipment_complete = 1




    --if statement is ok
    If @ErrorCode <> 0 ---and @intRowCount > 0
    --rollback does NOT work
    rollback tran


    END



    return (0)

  2. #2
    Join Date
    Jul 2002
    Posts
    9
    I would take a stab and say name your transaction and rolback the name. there are some examples in BOL with the exact syntax.


    begin transaction name
    rollback transaction name

  3. #3
    Join Date
    May 2002
    Posts
    395

    rollback examples

    Originally posted by acollins74
    I would take a stab and say name your transaction and rolback the name. there are some examples in BOL with the exact syntax.


    begin transaction name
    rollback transaction name
    Where can I find the BOL ?



  4. #4
    Join Date
    Jul 2002
    Posts
    9
    Open up your sql server and hit f1.

  5. #5
    Join Date
    Feb 2002
    Location
    Houston, TX
    Posts
    809
    alicejwz you need a begin trans statment.

    Also you do not have to name your transactions unless you wish to issue a commit or rollback to a point beyond the last begin trans.
    Paul Young
    (Knowledge is power! Get some!)

  6. #6
    Join Date
    May 2002
    Posts
    395
    Originally posted by Paul Young
    alicejwz you need a begin trans statment.

    Also you do not have to name your transactions unless you wish to issue a commit or rollback to a point beyond the last begin trans.
    Thank you much!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •