August 10, 2001 at 5:00 am
I have a stored Procedure to calculate Percentiles of a Numeric field.The stored Procedure works fine most of the times but at times it crashes giving errors in which it gives same values for a set of different parameters.Lgically there does not seem to be any problem.After this if I go in Enterprise Manager and Save the Stored Procedure all over again it starts working fine.There is no fixed pattern for this probelm to start.At times it starts after the stored Procedure executes 20 times and at times after 40 times stored Procedure has been executed.
I am using Scroll Cursors in Stored Procedures.Any leads will be greatly appreciated.I am using SQL 6.5.I have tried using RECOMPILE option but it does not seem to help.
Please feel free to mail me at sxnandwa@hewitt.com
August 10, 2001 at 6:28 am
That's a new one to me. Could you post the sp code, and provide some sample input so we can test on other installs?
Thanks,
Sean
August 17, 2001 at 4:17 am
Sorry for the delay in responding.
I could solve the problem by just changing the sequence of the cursor being opened but I still seem to be having issues with stored Procedures.Take for example the simple stored Procedure for which the code is below.
CREATE PROCEDURE SpTry1 AS
Set NoCount On
DECLARE @IntLop Int
Select @Intlop=0
Select @Intlop=1
While @IntLop < 10000
Begin
Select @IntLop
Select "Sharad"
Select @INtlop=@IntLop+1
End
GO
The stored Procedure is expected to execute 10000 times.However it only runs for 8156 times and it stops.If I close the connection and start again then it works otherwise it does not.If the loop is reduced to 2000 then the same thing happens when you execute it 5th time and it stops mid way.I am invoking this Stored Procedure from Sql Enterise Manager and I Have SQL 6.5
August 17, 2001 at 8:41 am
Are you on SP5 or 6?
Steve Jones
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
August 21, 2001 at 11:47 pm
I think usage of transaction statements would fix the problem ,if the DML operations are performed in the loop and if the data is saved partially.Or please can you tell me what kind of statements you are using inside the loop
August 22, 2001 at 4:59 pm
Im not sure what you're doing there. I created the proc and ran, got a message in QA that some results were being dropped because resources were low - no wonder, I was in grid mode and each iteration would cause a couple more grids to be added. Replaced the code inside the begin/end with 'set @intlop=@intlop+1', ran to 10000 with no problem.
Maybe Im missing something?
Andy
August 22, 2001 at 7:29 pm
I was unable to duplicate the error. I got the same error that Andy did when using QA in grid mode. I'll see if we still have an old 6.5 install at the office, and try it there.
August 25, 2001 at 12:52 am
The question is of just academic interest to me.What I am trying is very simple.I am just trying to ascertain if there is any output buffer which gets choked after it send out x number of results and if there is any method of refreshing it.I am using SQL server 6.5 and Service Pack 6.Thanks for all help.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply