Microsoft KB Archive/172572: Difference between revisions

From BetaArchive Wiki
(importing KB archive)
 
m (Text replacement - "<" to "<")
Line 80: Line 80:
   OPEN myCursor
   OPEN myCursor
   FETCH NEXT FROM myCursor INTO @c1, @c2
   FETCH NEXT FROM myCursor INTO @c1, @c2
   WHILE (@@FETCH_STATUS &lt;&gt; -1)
   WHILE (@@FETCH_STATUS <&gt; -1)
   BEGIN
   BEGIN
     IF (@@FETCH_STATUS &lt;&gt; -2)
     IF (@@FETCH_STATUS <&gt; -2)
       BEGIN
       BEGIN
         SELECT
         SELECT

Revision as of 16:17, 20 July 2020

Knowledge Base


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

Article ID: 172572

Article Last Modified on 10/3/2003



APPLIES TO

  • Microsoft SQL Server 6.5 Standard Edition



This article was previously published under Q172572

BUG #: 17132

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: kbbug kbusage KB172572