My cursor is always READ ONLY - WHY?

[CInman]

I use the following code:

DECLARE curItem CURSOR FOR SELECT MH_Desc FROM Item FOR UPDATE
OPEN curItem
FETCH NEXT FROM curItem
WHILE (@@FETCH_STATUS=0)
BEGIN
... etc ...

and when I run it, I am told:

Server: Msg 16929, Level 16, State 6, Line 7
The cursor is READ ONLY.

The error seems to relate to a line where I am trying to update an entry using "... WHERE CURRENT OF ..." and I understand my cursor is ReadOnly - however I have specifically stated it to be updatable - what am I missing? How can I check to make sure my cursor is updatable?

 

[Don803]

I found a reference that may shed some light.
It is in the Transact-SQL help file from SQL 7.0
sqlbol.chm
---------------------------------------
If a DECLARE CURSOR using Transact-SQL syntax does not specify READ_ONLY, OPTIMISTIC, or SCROLL_LOCKS, the default is as follows:

· If the SELECT statement does not support updates (insufficient permissions, accessing remote tables that do not support updates, and so on), the cursor is READ_ONLY.
· STATIC and FAST_FORWARD cursors default to READ_ONLY.
· DYNAMIC, and KEYSET cursors default to OPTIMISTIC.

-------------------------------
The default appears to be read only.

Look it up under declare cursor.

You would want DYNAMIC or Keyset for an updateable cursor.

 

[SQLSister]

Note also that it could be a permissions problem based on the quote above. What are you trying to update, maybe it doesn't need a cursor at all. Many updates that are done through cursors can be handled with a case statement in the update instead.