January 7, 2009 at 1:33 pm
I have a SP that creates a temporary table whose rows (1000+) are looped through with a WHILE statement. Within the WHILE LOOP a SP is called to retrieve an incrementing id, which is then inserted into a table. I find that the WHILE loop consistently "breaks" after 99 rows are processed. I've used a SQLProfiler to determine if an error is occurring; no error. IF I remove the call of the SP, the loop completes as expected.
The called SP queries a table to retrieve a value and then updates the value by one.
Any thoughts on the cause?
declare @iUserID int, @ilocationID int, @iCompanyI int
declare @iPKID int, @iSKID int
declare @tDataTable table (primary_key INT IDENTITY(1,1) NOT NULL, primary_id int, secondary_id int)
insert into @tDataTable (primary_id,secondary_id)
select u.memberid, u.cid
from members u with (NOLOCK)
left outer join Hiringlocation hs
on u.memberid = hs.memberid and hs.seq_no = @iSeq
where hs.seq_no is null
DECLARE @iPrimary_Counter INT
DECLARE @iLoop_counter INT
SET @iLoop_counter = ISNULL((SELECT COUNT(*) FROM @tDataTable),0) -- Set the @iLoop_counter to the total number of rows in the
SET @iPrimary_Counter = 1
WHILE @iLoop_counter > 0 AND @iPrimary_Counter <= @iLoop_counter
BEGIN
SELECT @iPKID = primary_id, @iSKID = secondary_id
FROM @tDataTable
WHERE primary_key = @iPrimary_Counter
execute mysp_GetCurrentValue location, @nextid = @ilocationID output
insert into Hiringlocation (locationid, memberid, locationName, seq_no, cid)
values (@nextid, @iPKID, @slocationName, @iSeq, @iSKID)
SET @iPrimary_Counter = @iPrimary_Counter + 1
END
January 7, 2009 at 1:45 pm
January 7, 2009 at 2:06 pm
the "location" is a passed value indicating what id needs to be incremented. I neglected to include that that SP is being called from an ASP. If the SP is executed in Query Analyzer the loop does not break.
January 7, 2009 at 2:49 pm
How are you calling the stored procedure from ASP?
Can you provide a Code snippet?
Simon
January 8, 2009 at 10:23 am
simple ASP:
function GetDBConnection()
dim tmp
set tmp = server.CreateObject("ADODB.Connection")
tmp.Open GetConnectionString()
set GetDBConnection = tmp
end function
function GetConnectionString()
GetConnectionString = "DSN=database;UID=username;PWD=password;"
end function
dim dbConn
set dbConn = GetDBConnection()
call dbConn.execute("exec mysp")
January 8, 2009 at 10:46 am
I dont see lot of information to go on.
You say that the SP works when you see in query analyzer - but we do know that it works 99 times
from the normal ASP page too.
So my question again is: can you run the SP in a Loop 101 times from query analyzer?
declare @counter int
set @counter= 0
while @counter < 101
begin
print @counter
execute mysp_GetCurrentValue location, @nextid = @ilocationID output
print @@error
select @counter = @counter + 1
end
January 8, 2009 at 10:49 am
I can execute the SP 1300 times using a while loop in QA w/o issue.
January 8, 2009 at 10:59 am
January 8, 2009 at 11:03 am
Thank you for your review.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply