Updatable Cursor

Question

Post reply

Updatable Cursor

William Young

SSC Enthusiast

Points: 145
More actions
September 1, 2004 at 7:24 am

#59312

Hello everyone:
Please be gentle on a SQL*Server newbie!
I am writing a stored procedure that needs the use of an updatable cursor.
But every time I attempt to add the "FOR UPDATE" clause to the cursor definition, I get:
Server: Msg 16957, Level 16, State 4, Line 3
FOR UPDATE cannot be specified on a READ ONLY cursor.
So, I need to know what the magic is to define an updatable cursor.
Can anyone help me out with a good example of one?
BTW: There are no permission issues that I can see. I do the same thing for another table in the database, it works just fine. There are no permissions on either of the tables, at both the table and column levels.
Thanks in advance!
Bill Young

Viewing 5 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Aaron Templeton SSCarpal Tunnel Points: 4655 More actions · Answer 1

Look at the DECLARE CURSOR information within books online to get all that stuff explained. Following is a template

DECLARE curUpdate CURSOR SCROLL_LOCKS DYNAMIC LOCAL FOR

SELECT something

FROM somewhere

WHERE some = condition

ORDER BY Location

FOR UPDATE

OPEN curUpdate

FETCH FIRST FROM curUpdate INTO @something

while @@FETCH_STATUS = 0

begin

UPDATE somewhere

SET OtherThing = 'That'

WHERE CURRENT OF curUpdate

FETCH NEXT FROM curUpdate INTO @something

end

CLOSE curUpdate

DEALLOCATE curUpdate

William Young SSC Enthusiast Points: 145 More actions · Answer 2

Aaron Templeton:

Thank you for your response!

I have done exactly what you showed, multiple times, with no success. Which is why I am pulling my hair out on this. This should be a no-brainer.

Here is what I've done:

declare apvc_cursor cursor scroll_locks dynamic local for

SELECT AlternateKey

, DocumentNumber

, DocumentType

, PayItem

, Company

, BusinessUnit

, ObjectAccount

, Subsidiary

FROM APVouchersChecks

WHERE ProfitCenterID = 1

AND GrossAmount > 0

AND InvoiceDate = convert( varchar(10), getdate(), 11 )

ORDER BY AlternateKey

, DocumentNumber

Adding "FOR UPDATE" to the above cursor definition results in the error shown.

Any other suggestions, from you or anyone else?

Bill Young

Aaron Templeton SSCarpal Tunnel Points: 4655 More actions · Answer 3

My first guess is that it may be resolving into an intermediate resultset for the purposes of performing a sort or resolving some expression. In other words, your cursor is not on the table but is instead on an internally temporary table SQL Server generated for the purpose of doing the sort (which is transient and therefore READ ONLY). Try droping the ORDER BY. If it still fails try a hard coded date, but I don't really see that causing the problem. If the ORDER BY is the problem perhaps you can do without it. If not you may need to use a different index or an index hint (it may be performing a table space scan due to the table being small). Once you get an implicite sort due to the access path you will avoid the intermediate resultset and should then be able to obtain an UPDATE cursor on it.

Should the above be to no avail please provide the DDL for the table and index structure of APVouchersChecks.

William Young SSC Enthusiast Points: 145 More actions · Answer 4

Aaron Templeton:

Bingo! The ORDER BY was indeed the issue.

I've gotten around this issue another way. But I just had to know why it wasn't working!

Thanks for the assistance!

Later!

Bill Young