Article ID: 172309
Article Last Modified on 3/14/2006
APPLIES TO
- Microsoft SQL Server 6.5 Standard Edition
This article was previously published under Q172309
BUG #: 17129 (SQLBUG_65)
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 SQL Server 6.5. This problem has been corrected in Service Pack 5a for Microsoft SQL Server 6.5.For more information, click the following article number to view the article in the Microsoft Knowledge Base:
197177 INF: How to Obtain SQL Server 6.5 Service Pack 5a
For more information, contact your primary support provider.
Additional query words: prodsql sp sp5
Keywords: kbbug kbfix kbprogramming kbusage KB172309
