PRB: Dynamic Cursor Infinite Loop When a Non-Unique Clustered Index Key Is Updated to an Equal or Larger Value (280406)


The information in this article applies to:

  • Microsoft SQL Server 7.0
  • Microsoft SQL Server 2000 (all editions)
This article was previously published under Q280406

SYMPTOMS

A dynamic cursor goes into an infinite loop when a non-unique clustered index key is updated to a value greater than or equal to itself.

CAUSE

For a non-unique clustered index key update, if the new value is greater than or equal to the original value, the update actually deletes the original value and inserts the new value. This adds a new row, which is reflected into the dynamic cursor and causes the @@fetch_status to always return '0'.

WORKAROUND

Define the index as nonclustered instead of clustered.

MORE INFORMATION

This behavior is by design. The following example demonstrates the behavior. 
Create table testtable(col1  int null, col2  int null, col3  int null )
go

Create clustered index clustindex on testtable  (col1,col2,col3) 
go

insert into testtable values(100,100, 100)
go

declare mycursor cursor dynamic
for 
select * from testtable
for update of col1,col2 ,col3
open mycursor 
declare @count integer
declare @f1 integer
declare @f2 integer
declare @f3 integer

fetch mycursor into @f1,@f2,@f3
select @count = 0
while @@fetch_status = 0
begin  
   update testtable
   set col1 = 300, col2 = 300, col3 = 300
   where current of mycursor 
   fetch mycursor into @f1,@f2,@f3
end

close mycursor 
deallocate mycursor 
go
Modification Type:MajorLast Reviewed:10/16/2003
Keywords:kbprb KB280406
©2003 Microsoft Corporation. All rights reserved.