Post reply

Records missing

  • March 25, 2010 at 2:30 am

    #222341

    Hi,

    I am facing a peculiar situation and require some help.

    We need to insert records in a table for 10 employees which is done through a stored procedure.

    This has a Begin and Rollback transaction. While this is looped through to call the procedure 10 times,

    i could see the data in the table using nolock hint. however, once the loop is over, all the data get rolled back and we dont have a wrapper transaction too in the calling method. How is this possible?

  • March 25, 2010 at 2:42 am

    #1139455

    This has a Begin and Rollback transaction.

    Are you actually specifying a COMMIT in your code?

  • March 25, 2010 at 3:01 am

    #1139460

    Yes, by default we will commit which is available in the BEGIN TRY...END TRY block...

    ROLLBACK in CATCH block... but this is within the stored procedure which is called n number of times. Only after the nth time execution all the inserted data gets vanished. Till then i am able to view the n-1, n-2 records using nolock hint.

  • March 25, 2010 at 3:09 am

    #1139464

    This problem surfaced as soon as we ran sp_updatestats. Now is there any link with this?

  • March 25, 2010 at 7:09 am

    #1139597

    I doubt it has anything to do with statistics. You're either hitting an error that is causing the rollback, the transaction is rolling back because the connection was dropped, or you have an error in your code that is not calling COMMIT.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • March 26, 2010 at 6:16 pm

    #1140845

    I agree with Grant, this has the feel of a logic bug. You can post the procedure's code if you would like us to check it.

    [font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
    Proactive Performance Solutions, Inc.     [/font]    [font="Verdana"] "Performance is our middle name."[/font]

  • March 26, 2010 at 8:33 pm

    #1140867

    Apparently this got fixed with a SQL server restart...

  • March 27, 2010 at 2:06 pm

    #1140982

    Sudarsan Srinivasan (3/26/2010)

    Apparently this got fixed with a SQL server restart...

    All that's left to do is get rid of the loop and you're golden! 😉

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • March 27, 2010 at 3:59 pm

    #1141000

    -- my opinion

    it has nothng to do with statistics or restating the server.. its transaction handling issue. logic not written correctly

  • March 28, 2010 at 6:16 am

    #1141070

    vidya_pande (3/27/2010)

    it has nothng to do with statistics or restating the server.. its transaction handling issue. logic not written correctly

    Agreed. I think Sudarsan Srinivasan will be seeing this problem again;-)

    Paul White
    All articles available on SQL.kiwi
    @SQL_Kiwi

  • March 29, 2010 at 4:25 am

    #1141292

    Sudarsan Srinivasan (3/25/2010)

    ROLLBACK in CATCH block...

    -------Bhuvnesh----------
    I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

  • March 29, 2010 at 4:27 am

    #1141295

    Sudarsan Srinivasan (3/25/2010)

    ROLLBACK in CATCH block...

    What else you have in CATCH box ? means any error message or error code syntax?

    Bcoz that will help you/us to get the exact failure reason if it is failing and then ROLLBACK is happened.

    -------Bhuvnesh----------
    I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

Viewing 12 posts - 1 through 11 (of 11 total)

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