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

• Microsoft SQL Server version 6.5

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 version 6.5. This problem has been corrected in U.S. Service Pack 5a for Microsoft SQL Server version 6.5. For information about downloading and installing the latest SQL Server Service Pack, see http://support.microsoft.com/support/sql/.

For more information, contact your primary support provider.

Additional query words: prodsql sp sp5

Keywords          : kbprg kbusage SSrvTran_SQL kbbug6.50 kbfix6.50.SP5 
Version           : winnt:6.5
Platform          : winnt 
Issue type        : kbbug 

Last Reviewed: June 24, 1999