Microsoft KB Archive/280406

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

Article ID: 280406

Article Last Modified on 10/16/2003

-

APPLIES TO


 * Microsoft SQL Server 7.0 Standard Edition
 * Microsoft SQL Server 2000 Standard Edition

-



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

Additional query words: non-clustered

Keywords: kbprb KB280406

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.