FIX: Infinite Loop When Trying to UPDATE a cursor with a WHERE CURRENT OF Clause (172309)

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.