Infinite Cursor Loop Error
-
Hi, i have got a hierarchy of samples that have activities carried out on them. when i edit a batch of samples in my application i create a copy of all the samples in that batch and all its activities etc etc and un-reference the old sample, thus keeping track of any changes being made. I have got a "batches", "samples" and an "activities" table.
To do this i used a cursor and inserted into a temporary table to test that everything worked as it should, before inserting into the samples table. This all worked fine so instead of inserting into the temporary table i changed it to insert into the "samples" table and now my code doesn't work properly and it continues to loop through the cursor until i kill it, thus inserting around 50,000 samples insetad of just 1.
I can't understand why it is doing this as i only changed the table name that it was inserting into, from "@tempSamples" to "samples". also the cursor is enclosed in a transacion so this further confuses me, as i was under the impression it would not commit the data if i stopped the code executing in query analzer.
Any suggestions as to how to fix this problem would be much appreciated as it's driving me mad. Here is my simplified SQL:
DECLARE @batchID int
SET @batchID = 49
--Sample Vers
DECLARE @nsampleID int
DECLARE @nsampleNumber varchar(50)
DECLARE @nbatchID int
DECLARE @ndescription varchar(250)
DECLARE @nenteredQAID int
DECLARE @neditedQAID int
DECLARE @ndeletedQAID int
DECLARE @ncompletedQAID int
DECLARE @NewSampleID int
DECLARE @OLDSampleID int
-- Samples for the batch
DECLARE @tempSamples TABLE
(
sampleID [int] IDENTITY (1, 1) NOT NULL,
sampleNumber varchar(50),
batchID int,
[description] varchar(250),
enteredQAID int,
editedQAID int,
deletedQAID int,
completedQAID int
)
BEGIN TRANSACTION
DECLARE Sample_Cursor CURSOR FOR
SELECT sampleID, sampleNumber, batchID, [description], enteredQAID, editedQAID, deletedQAID, completedQAID
FROM samples
WHERE batchID = @batchID
OPEN Sample_Cursor
FETCH NEXT FROM Sample_Cursor INTO @nsampleID, @nsampleNumber, @nbatchID, @ndescription, @nenteredQAID, @neditedQAID, @ndeletedQAID, @ncompletedQAID
WHILE @@FETCH_STATUS = 0
BEGIN
INSERT INTO @tempSamples (sampleNumber, batchID, [description],enteredQAID, editedQAID, deletedQAID,completedQAID)
VALUES(@nsampleNumber, @nbatchID, @ndescription, @nenteredQAID, @neditedQAID, @ndeletedQAID, @ncompletedQAID)
SET @NewSampleID = (SELECT @@IDENTITY)
SET @OLDSampleID = @nsampleID
FETCH NEXT FROM Sample_Cursor INTO @nsampleID, @nsampleNumber, @nbatchID, @ndescription, @nenteredQAID, @neditedQAID, @ndeletedQAID, @ncompletedQAID
END
CLOSE Sample_Cursor
DEALLOCATE Sample_Cursor
----------------------------------------------------------------------------------------
--If any errors occured then do not commit the changes (roll Back)
----------------------------------------------------------------------------------------
If @@Error > 0
Begin
ROLLBACK TRANSACTION
End
Else
Begin
COMMIT TRANSACTION
End
select * from @tempSamples
-
ANSI SQL expert Peter Gulutzan says when you create a Cursor a query processor says you have asked me to perform a task I am not equipped to do so you think I need five loops I am going to take twenty. I guess yours just crossed over. The simple answer is try alternative solutions to get your results and sorry I don't do Cursors. Hope this helps.
Kind regards,
Gift Peddie
Kind regards,
Gift Peddie
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply