BUG: Fail and Break Out to UPDATE of a Cursor with WHERE Clause

• Microsoft SQL Server version 6.5

SYMPTOMS

If you perform an UPDATE of a cursor that contains a WHERE clause and if the underlying table does not have a primary key, the UPDATE may fail to continue because the cursor breaks out after the first fetch. 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)
   INSERT t VALUES (NULL, 3)
   INSERT t VALUES (NULL, 4)
   INSERT t VALUES (NULL, 5)

   DECLARE @c1 CHAR(10)
   DECLARE @c2 INT

   DECLARE myCursor CURSOR FOR
      SELECT c1, c2
      FROM t
      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 c2 = @c2
      END
      FETCH NEXT FROM myCursor INTO @c1, @c2
   END
   CLOSE myCursor
   DEALLOCATE myCursor 

WORKAROUND

You can avoid this problem by creating 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.

Additional query words: t-sql tran-sql tsql transql

Keywords          : kbusage SSrvTran_SQL kbbug6.50 
Version           : 6.5
Platform          : WINDOWS 
Issue type        : kbbug 

Last Reviewed: April 21, 1999