Microsoft KB Archive/172309

= FIX: Infinite Loop When Trying to UPDATE a cursor with a WHERE CURRENT OF Clause =

Article ID: 172309

Article Last Modified on 3/14/2006

-

APPLIES TO


 * Microsoft SQL Server 6.5 Standard Edition

-



This article was previously published under Q172309



BUG #: 17129 (SQLBUG_65)



SYMPTOMS
An UPDATE of a cursor with a WHERE CURRENT OF clause may cause an infinite loop if the DECLARE CURSOR statement includes a WHERE clause and the underlying table does not have a primary key. The following scripts demonstrate this problem: SET NOCOUNT ON GO

DROP TABLE t GO

CREATE TABLE t ( c1 CHAR(10) NULL, c2 INT NOT NULL ) GO

INSERT t VALUES (NULL, 1) INSERT t VALUES (NULL, 2)

DECLARE @c1 CHAR(10) DECLARE @c2 INT

DECLARE myCursor CURSOR FOR SELECT c1, c2 FROM t WHERE c2 = 1 FOR UPDATE

OPEN myCursor FETCH NEXT FROM myCursor INTO @c1, @c2 WHILE (@@FETCH_STATUS <> -1) BEGIN IF (@@FETCH_STATUS <> -2) BEGIN SELECT '@@FETCH_STATUS' = CONVERT (VARCHAR(10), @@FETCH_STATUS), 'C1' = CONVERT (VARCHAR(10), @c1), 'C2' = CONVERT (VARCHAR(10), @c2) UPDATE t SET c1 = 'updated' WHERE CURRENT OF myCursor END FETCH NEXT FROM myCursor INTO @c1, @c2 END CLOSE myCursor DEALLOCATE myCursor



WORKAROUND
You can avoid this problem if you create the table with a primary key. The following scripts demonstrate the workaround for this problem: CREATE TABLE t ( c1 CHAR(10) NULL, c2 INT NOT NULL PRIMARY KEY ) GO



STATUS
Microsoft has confirmed this to be a problem in SQL Server 6.5. This problem has been corrected in Service Pack 5a for Microsoft SQL Server 6.5.For more information, click the following article number to view the article in the Microsoft Knowledge Base:

197177 INF: How to Obtain SQL Server 6.5 Service Pack 5a

For more information, contact your primary support provider.

Additional query words: prodsql sp sp5

Keywords: kbbug kbfix kbprogramming kbusage KB172309

-

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

© Microsoft Corporation. All rights reserved.