Results 1 to 13 of 13
  1. #1
    Join Date
    Oct 2004
    Posts
    16

    Unanswered: Am I locking it up...

    Ok, this may be a brain dead question but I can't seem to figure out what it is I am doing wrong. I have a stored proc which has multiple inserts and updates and deletes. However, I do not want to commit until the end of the procedure. So near the end if no error has been return by a particular insert, update, delete I tell it to COMMIT TRAN. My problem is that it seems to run and run and run and run. I take out the Begin Tran and boom it runs fast and completes.

    But if there is a problem near the end then those other statements will be committed. I wish to avoid that. I have an error routine at the end of the SP and I have if statement to GOTO sp_error: if @@error produces a non zero value. I am sure I am doing something goofy but can seem to see it. I know it has come down to the Begin Tran. Is it that I have too many uncommitted transactions? Or perhaps I am locking something up. I know its hard to tell without seeing what I am doing but is there something simple to remember about using explicit transactions that I am forgetting. Any help is appreciated.

    Tom

  2. #2
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    what do you see when you do sp_who2?

    Any blocking?

    Do you call any other sprocs?

    Do you execute any extended procs?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  3. #3
    Join Date
    Oct 2004
    Posts
    16
    Nope I do not see any blocking. No, but will and no. Any ideas?

    I did find this. By the way I am using your template.

    Server: Msg 2732, Level 16, State 1, Procedure CreateParContracts, Line 879
    Error number 0 is invalid. The number must be from 13000 through 2147483647
    This happens when I reach the sp_error lable at the end of stored proc. Here is the code

    SP_Error:
    If @Error_Type = 50001
    BEGIN
    Select @error_message = (Select 'Location: ' + ',"' + RTrim(Convert(char(3),@Error_Loc))
    + ',"' + ' @@ERROR: ' + ',"' + RTrim(Convert(char(6),error))
    + ',"' + ' Severity: ' + ',"' + RTrim(Convert(char(3),severity))
    + ',"' + ' Message: ' + ',"' + RTrim(description)
    From master..sysmessages
    Where error = @error_out)
    END
    If (@Error_Type = 50002)
    BEGIN
    Rollback TRAN
    Select @Error_Message = 'Location: ' + ',"' + RTrim(Convert(char(3),@Error_Loc))
    + ',"' + ' Severity: UserLevel '
    + ',"' + ' Message: ' + ',"' + RTrim(@Error_Message)
    END
    RAISERROR @Error_Type @Error_Message


    the line 879 seems to be pointing to if statement "If @Error_Type = 50001"
    Last edited by tlbrack; 11-16-04 at 15:45.

  4. #4
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Can you attach the sproc so we can look at it?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  5. #5
    Join Date
    Oct 2004
    Posts
    16
    Ok I believe I have attached the file. Actually I did not orginally produce this script. It was just a set of t-sql code being run each time they need to load these. I am trying to automate the process. Couple of things.

    1. The attachment is not the one I could get to compile. I kept getting a Server: Msg 133, Level 15, State 1, Procedure CreateParContracts, Line 862
    A GOTO statement references the label 'SP_Error' but the label has not been declared.

    ??

    2. If I remove the sp_exit and just put it the end of the stored procedure then I have the snippet of code that I just posted. So I don't know. Looks ok to me but I have something wrong.

    Thanks Brett

    Tom
    Attached Files Attached Files
    Last edited by tlbrack; 11-16-04 at 16:08.

  6. #6
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    I'm looking....

    first very bad thing is the creation of a permanent table...what if the sproc is run at the same time...change that to use a temporary table...and even though the temp table goes away at the end of the batch, it's always a good idea to drop it on the way out...

    I'm still looking
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  7. #7
    Join Date
    Oct 2004
    Posts
    16
    Well, I agree but i didn't think it would hurt anything leaving it in since it would get run once a month and by one person. But, if this were to be used by more than one user than yes it has to use a temp table. As matter of fact I think it would better to just rewrite the whole thing. Since there are a lot of things that bother me about this script.

  8. #8
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Yeah..there are around 25 separate steps...

    And where did you get that code? Error location btw is a debugging tool I use when I want to know which statement failed..I updated them..

    Try this attachment
    Attached Files Attached Files
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  9. #9
    Join Date
    Oct 2004
    Posts
    16
    It was just a script file that was being run with 4 other script files to process this data. The one you looked at was the first one that got ran. There are four others. This is all processed into files that get loaded to a AS400. As stated earlier I decided to try and create a sp for each script file. Then run all 5 from a containter. This may not work as they are. But I really don't have the time to rewrite them. I wonder if it will hang now. I will try it out. Thanks a lot for your help. I will let know if I get it working.

    Thanks
    Tom

  10. #10
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Good Luck...You might want to consider breaking it up.

    BUT Where did you get that code?

    That's definitely part of my template and definetly my error handling...

    My Blog Entry

    Now I gotta make sure it runs
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  11. #11
    Join Date
    Oct 2004
    Posts
    16
    I was afraid of this but now it just keeps running and running and running. It might have something to do with some code trying to delete or change data that as yet to be committed from being inserted before. Would that make sense? If I move the Begin Tran down near the end it runs fine.

    Tom

  12. #12
    Join Date
    Oct 2004
    Posts
    16
    Yes, I got the error handleing from your template. The rest is a mixture of what was there and what I normally put in. Perhaps I am not understanding question.

  13. #13
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Doesn't make sense......if you didn't have any at all, it is implicitly 1 transaction anyway for that spid...and would rollback with an error that was severe enough....

    Run the sproc with the BEGIN TRAN at the top...

    And while it's just sitting there...run sp_who2 to see what's up...

    You might want to start profiler as well (On a client)
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

Posting Permissions

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