Microsoft KB Archive/172572: Difference between revisions
m (Text replacement - "<" to "<") |
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 < | WHILE (@@FETCH_STATUS <> -1) | ||
BEGIN | BEGIN | ||
IF (@@FETCH_STATUS < | IF (@@FETCH_STATUS <> -2) | ||
BEGIN | BEGIN | ||
SELECT | SELECT |
Latest revision as of 21:18, 20 July 2020
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