By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
445,645 Members | 1,065 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 445,645 IT Pros & Developers. It's quick & easy.

SqlServer changes cursor to "read only"

P: n/a
I am trying to write a cursor to update certain rows in a particular
order as follows: (I need the cursor version, not SQL, as the update
logic depends on the order of rows and some other conditions. I
removed the Order-By clause from the statement to simplify it; it
gives the same error message with or without it.)

DECLARE prod_cursor CURSOR
FORWARD_ONLY
KEYSET
FOR SELECT 1 FROM all_products WHERE p_qty = 0 FOR UPDATE

This gives the following error message: "FOR UPDATE cannot be
specified on a READ ONLY cursor."

I have tried a few different combinations of cursor types (like SCROLL
instead of FORWARD_ONLY) but they all give this error, although the
statement seems identical to what I have seen in the books and in
books online.

Any ideas on how to convert this into an updating cursor?
Jul 20 '05 #1
Share this Question
Share on Google+
2 Replies


P: n/a

"php newbie" <ne**********@yahoo.com> wrote in message
news:12**************************@posting.google.c om...
I am trying to write a cursor to update certain rows in a particular
order as follows: (I need the cursor version, not SQL, as the update
logic depends on the order of rows and some other conditions. I
removed the Order-By clause from the statement to simplify it; it
gives the same error message with or without it.)

DECLARE prod_cursor CURSOR
FORWARD_ONLY
KEYSET
FOR SELECT 1 FROM all_products WHERE p_qty = 0 FOR UPDATE

This gives the following error message: "FOR UPDATE cannot be
specified on a READ ONLY cursor."

I have tried a few different combinations of cursor types (like SCROLL
instead of FORWARD_ONLY) but they all give this error, although the
statement seems identical to what I have seen in the books and in
books online.

Any ideas on how to convert this into an updating cursor?


Assuming that your real SQL statement has "SELECT col1, col2, ..." and not
"SELECT 1", then one possibility is that the table all_products has no
unique index or primary key. If there is no unique index, then keyset
cursors are converted to read-only - see "Implicit Cursor Conversions" in
Books Online. That section also lists a number of other reasons why your
cursor may be converted to a static (read-only) one.

If this doesn't help, perhaps you could post the complete DDL for
all_products, including constraints and indexes.

Simon
Jul 20 '05 #2

P: n/a
php newbie (ne**********@yahoo.com) writes:
I am trying to write a cursor to update certain rows in a particular
order as follows: (I need the cursor version, not SQL, as the update
logic depends on the order of rows and some other conditions.
That's is far from certain. Usually, if you are creative enough, you
can find more effective ways around it. I don't know about your case,
but another posting had a similar case, to which I made a suggestion.
Have a look at:
http://groups.google.com/groups?hl=s...an%40127.0.0.1

Maybe this can get you started.
DECLARE prod_cursor CURSOR
FORWARD_ONLY
KEYSET
FOR SELECT 1 FROM all_products WHERE p_qty = 0 FOR UPDATE

This gives the following error message: "FOR UPDATE cannot be
specified on a READ ONLY cursor."

I have tried a few different combinations of cursor types (like SCROLL
instead of FORWARD_ONLY) but they all give this error, although the
statement seems identical to what I have seen in the books and in
books online.


Don't use FOR UPDATE. You only need this when you use UPDATE WHERE
CURRENT OF, and you could just as well use a regular WHERE clause.
Myself, I never use WHERE CURRENT OF.

As for the cursor type, I recommend INSENSITIVE CURSOR.

--
Erland Sommarskog, SQL Server MVP, so****@algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 20 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.