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

ID: Q158773

The information in this article applies to:

Microsoft SQL Server versions 6.0, 6.5

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 SSrvGen
Version : 6.0 6.5
Platform : WINDOWS
Issue type :