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.

Tomwhat do you see when you do sp_who2?

Any blocking?

Do you call any other sprocs?

Do you execute any extended procs?|||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"|||Can you attach the sproc so we can look at it?|||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. :eek:

Thanks Brett

Tom|||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|||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.|||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|||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|||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 (http://weblogs.sqlteam.com/brettk/archive/2004/05/25/1378.aspx)

Now I gotta make sure it runs|||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|||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. :confused:|||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)