Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

My cursor is always READ ONLY - WHY?

Status
Not open for further replies.

CInman

IS-IT--Management
Mar 15, 2001
81
GB
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?
 
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.
 
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.
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top