Microsoft KB Archive/158773

= INF: Explanation of Error Message 16929, Cursor Is Read-Only =

Article ID: 158773

Article Last Modified on 10/16/2003

-

APPLIES TO


 * Microsoft SQL Server 6.0 Standard Edition
 * Microsoft SQL Server 6.5 Standard Edition

-



This article was previously published under Q158773



SUMMARY
This article discusses the conditions that cause the following SQL Server error:

Msg 16929, Level 16, State 1

Cursor is read only



MORE INFORMATION
Message 16929 simply means that an update operation was attempted on a read- only cursor. Cursor update operations include doing an UPDATE, INSERT, or DELETE command specifying a WHERE CURRENT OF clause that references the read-only cursor.

By definition, an insensitive (or static) cursor is a read-only cursor, as is a cursor declared with the FOR READ ONLY clause. For example, a cursor declared as:

DECLARE xyz CURSOR FOR select * from authors

-or-

DECLARE abc SCROLL CURSOR FOR select * from authors

is updatable unless the select clause violates the restrictions on what SQL Server allows in an updatable cursor. The Transact-SQL Reference Manual section on cursors contains a description of the DECLARE statement. In the paragraphs discussing the select statement that can be specified as part of a DECLARE CURSOR command, the manual lists the limitations that prevent a cursor from being updatable. If a cursor is declared in SQL Server 6.0 with a select statement that does any of the following, the cursor will actually be created as a static, read-only cursor:


 * Does not reference any tables with at least one unique index or primary key constraint.
 * Contains DISTINCT, UNION, GROUP BY, or HAVING clauses.
 * Contains a constant expression in the select list.

You will receive message 16929 if you try to do an update operation (for example, doing an update, insert, or delete WHERE CURRENT OF the cursor) using such a cursor. In SQL Server 6.5, the requirement for a unique index or primary key constraint is dropped for the DECLARE xyz CURSOR statement.

For example, if you issue the following commands in SQL Server 6.0:

CREATE TABLE NoIDX (cola int, colb char(8)) go  DECLARE MyCursor SCROLL CURSOR FOR select * from NoIDX FOR UPDATE go

the cursor MyCursor will be created, but it will be a static, read-only cursor because the table NoIDX does not have a primary key or unique index. Any later attempt to do an update, delete, or insert WHERE CURRENT OF MyCursor would fail with message 16929. With SQL Server 6.5, the behavior was changed so that the DECLARE CURSOR command will fail with message 16929 because the user had specified FOR UPDATE in a cursor that cannot support updates.

The most common cause for error 16929 is a cursor that references tables that do not have primary key constraints or unique indexes. In this case, simply create a primary key constraint or unique index on the table. If the cause of error 16929 is that the select statement in the DECLARE CURSOR command violates the limitations for updatable cursors, then see if the select statement can be recoded.

If the table structure will not support a unique index, or if the select statement cannot be recoded, the cursor will not be updatable. This simply means that you cannot reference the cursor in a WHERE CURRENT OF clause on an UPDATE, INSERT or DELETE command. However, the cursor can still be used to determine the position for an UPDATE, INSERT, or DELETE. Instead of doing a FETCH to position on a row and then doing an UPDATE WHERE CURRENT OF, the you could do a FETCH to retrieve the key values identifying the row to be affected, and then specify the key values in the WHERE clause of the UPDATE command. For example, instead of doing the following on the NoIDX table (created in the example above):

DECLARE MyCursor SCROLL CURSOR FOR select * from NoIDX OPEN MyCursor FETCH FIRST FROM MyCursor UPDATE NoIDX SET colb = 'string' WHERE CURRENT OF MyCursor go

You could do the following:

DECLARE @CurCola INT DECLARE MyCursor SCROLL CURSOR FOR select cola from NoIDX OPEN MyCursor FETCH FIRST FROM MyCursor INTO @CurCola UPDATE NoIDX SET colb = 'string' WHERE cola = @CurCola go

Keywords: kbusage KB158773

-

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

© Microsoft Corporation. All rights reserved.