Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums  SQL Server 2000 Forums  SQL Server Development (2000)  stored proc won't run on ASP page

Author  Topic  paul13 Starting Member 17 Posts Posted - 2001-03-29 : 02:25:20 I have a stored procedure (sp) that runs fine on the query anylizer, but when I run it through an ASP page, it will not run to completion. The sp calls a cursor, and loops through the result, using the result of the cursor as parameters for executing 3 other stored procedures. CODE CREATE PROCEDURE ProcA @temptablename varchar(25) AS DECLARE @QStr varchar(250), @temptable varchar(26), @Combotable varchar(26) DECLARE CursA CURSOR FOR SELECT * FROM realcrittable DECLARE @comboaccount varchar(15), @actualaccount varchar(12), @oncrit varchar(255), @offcrit varchar(255), @docrit varchar(255) select @combotable = 'combo' + @temptablename select @temptable = @temptablename open CursA fetch CursA INTO @comboaccount, @actualaccount, @oncrit, @offcrit,@docrit while @@fetch_status = 0 BEGIN execute putParsedComboRuns @OnCrit, @ComboAccount, @ActualAccount, @temptable, 'passengerson', 'passengersoff' execute putParsedComboRuns @OffCrit, @ComboAccount, @ActualAccount, @temptable, 'passengersoff', 'passengersoff' execute putParsedComboDOs @DOCrit, @ComboAccount, @ActualAccount, @temptable, 'dohours', 'dohours' fetch CursA INTO @comboaccount, @actualaccount, @oncrit, @offcrit,@docrit END close CursA deallocate CursA The sp runs fine in query analyzer, but when I run the sp through an asp page, won't repeat the fetch, it runs the 3 stored procedures, and then stops. When ProcA is executed inside of another sp called by an ASP page, it causes the other sp to stop as well. the commandtime is up to 300 seconds, and the whole thing takes less than 30. All permissions have been set up so that should not be the problem, and if it was, the procedure run through the the loop once. I tried running a similar procedure, but instead of calling putParsedDOs, I put the code inside of ProcA, using a nested Cursor. Could the problem be that once @@fetch_status gets to 0 for one cursor, it gets out of the loop of the other? any ideas? Edited by - paul13 on 03/29/2001 03:20:42