Stored procedure only updates top 10 rows

Results 1 to 2 of 2

Thread: Stored procedure only updates top 10 rows

  1. #1
    Join Date
    Dec 1969

    Default Stored procedure only updates top 10 rows

    We are using a Stored Procedure to update statistics for football teams. It runs fine for the first 10 or so players and then just seems to quit. If I run it from SQL Server Query Analyzer, it runs all the way through. But if I call it from my ASP script it only does about 10-12 of the players. Any suggestions? <BR>Below is an example of what we are doing (all the variables have been declared, I just didn&#039t include them):<BR><BR><BR>-- calculate and write level 2, player totals for season<BR><BR>declare abc cursor for <BR>Select distinct playerid from footballstats <BR>where teamid=@teamid and seasonid=@seasonid and levelid=1<BR>open abc<BR>fetch next from abc<BR>into @pid<BR>while @@fetch_status=0<BR><BR>Begin<BR><BR>-- Check season totals record exists for this player, if not insert it.<BR><BR>declare aef cursor for <BR>Select playerid from footballstats <BR>where playerid=@pid and seasonid=@seasonid and levelid=2<BR>open aef<BR>fetch next from aef<BR>if @@fetch_status&#060;&#062;0<BR>begin<BR>Insert Into footballstats (levelid, teamid, seasonid, playerid)<BR>Values (2, @teamid, @seasonid, @pid)<BR>end<BR>close aef<BR>deallocate aef"

  2. #2
    Join Date
    Dec 1969

    Default RE: Stored procedure only updates top 10 rows

    I had a similar problem once... <BR><BR>SELECT Queries in my asp app were returning 10 records when they should&#039ve been returning more. after much searching, I found that one page of my app was passing "SET ROWCOUNT 10" to display a top ten list. After the page was viewed, the connection was released and put back into the Connection Pool. That connection still had the "SET ROWCOUNT 10" active. When another page went to the connection pool, it (sometimes) was getting that same connection with the rowcount set to 10. Running queries thru Query analyzer wouldn&#039t show a problem since it (the query analyzer) was using a different connection. <BR><BR>Don&#039t know if this applies but something to look for... <BR><BR>Hope that helps <BR><BR><BR>p.s I gave the same response at<BR>

Posting Permissions

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