TSQL Stored Procedure giving Problems.

  • 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

  • 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?



  • 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.


    Set NoCount On

    DECLARE @IntLop Int

    Select @Intlop=0

    Select @Intlop=1

    While @IntLop < 10000


    Select @IntLop

    Select "Sharad"

    Select @INtlop=@IntLop+1



    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

  • Are you on SP5 or 6?

    Steve Jones


  • 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

  • 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?


  • 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.

  • 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