BUG: Infinite Loop When UPDATE of a Cursor with WHERE CURRENT OF

• 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 Microsoft SQL Server version 6.5. We are researching this problem and will post new information here in the Microsoft Knowledge Base as it becomes available.

Keywords          : kbbug6.50 SSrvTran_SQL kbprg kbusage
Version           : 6.5
Platform          : WINDOWS
Issue type        : kbbug
Solution Type     : kbworkaround