Microsoft KB Archive/172572

= 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

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.