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

    Unanswered: rollback in t-sql

    The rollback transaction does not return to original state in my stored procedure. I am developing my application in Access 2000 fe and SQL Server be. I just can't see the problem.

    Alter Procedure SpZ
    as
    declare @exit_while bit, @work_ord_num char(9), ......
    Declare ship_sched CURSOR

    FOR
    SELECT work_ord_num, ... FROM tblShipping_sched
    ORDER BY shipped_qty DESC;

    OPEN ship_sched
    FETCH NEXT FROM ship_sched INTO @work_ord_num, ...
    WHILE @shipped_qty IS NOT NULL and @@FETCH_STATUS = 0

    Select @ErrorCode = @@Error
    BEGIN
    Set @ErrorCode = 1
    insert into tblShipment_history (work_ord_num, .....) VALUES (@work_ord_num, .....);


    If @ErrorCode <> 0
    rollback tran

    END


    Thanks!!

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    I'm not sure why your code runs at all, though I realize you didn't include all of it.
    You don't have any FETCH NEXT statement after your WHILE statement, so I would think this would spin into an infinite loop.
    If your WHILE statement does work, then it will only loop through the "Select @ErrorCode = @@Error" statement, and not through your BEGIN..END code block.

    I don't see why the BEGIN..END code block doesn't result in a rollback, since @ErrorCode is always = 1, but you try simplifying the procedure by eliminating the cursor (cursors are bad) and use an insert statement like this:

    insert into tblShipment_history (work_ord_num, .....)
    select work_ord_num, .....
    from tblShipping_sched
    where shipped_qty IS NOT NULL

  3. #3
    Join Date
    May 2002
    Posts
    395

    rollback is not working in T-SQL

    Originally posted by blindman
    I'm not sure why your code runs at all, though I realize you didn't include all of it.
    You don't have any FETCH NEXT statement after your WHILE statement, so I would think this would spin into an infinite loop.
    If your WHILE statement does work, then it will only loop through the "Select @ErrorCode = @@Error" statement, and not through your BEGIN..END code block.

    I don't see why the BEGIN..END code block doesn't result in a rollback, since @ErrorCode is always = 1, but you try simplifying the procedure by eliminating the cursor (cursors are bad) and use an insert statement like this:

    insert into tblShipment_history (work_ord_num, .....)
    select work_ord_num, .....
    from tblShipping_sched
    where shipped_qty IS NOT NULL
    You were right. My code was not working it was going through an infinite loop. I took your advice and remove cursor in my stored procedure now I have part of code working, the rollback statement is still not working. Could you take a look again?

    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)

  4. #4
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Where is your BEGIN TRANSACTION statement?
    Where is your COMMIT TRANSACTION statement?

    Also, I assume the select @ErrorCode = 1 statement is just for testing purposes? It will prevent
    If @ErrorCode = 0 and @intRowCount > 0
    DELETE FROM tblShipping_sched WHERE shipment_complete = 1
    from ever executing.

    blindman

Posting Permissions

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