473,321 Members | 1,748 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,321 software developers and data experts.

SqlServer changes cursor to "read only"

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
2 13374

"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
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

1
by: Fie Fie Niles | last post by:
I have IIS installed on XP Professional workstation machine. I have an ASP page that open connection to an Access database, then when trying to update the database, it gave me the error "cannot...
0
by: Bennett Haselton | last post by:
I'm trying to port my MySQL tables for a database called "tracerlock" from one server to another. On the old server, in the /var/lib/mysql/tracerlock directory, there was a ".MYD", ".MYI" and...
7
by: Rich | last post by:
Hi, I'm having problems with changing the Read Only properties. Running Winxp and i cannot get the "read only" to clear off a folder. The folder is one that i want to change the data in and it...
3
by: Vic | last post by:
Dear All, I have a database of laboratory records in Access 2000. There is one form which acts as an interface to input experimetal data. This form incorporates information from several tables....
3
by: Johannes Zellner | last post by:
Hi, can I make an object read-only, so that x = new_value fails (and x keeps it's orginal value)? This would be especially of interest for me for an object created by a c extension.
1
by: Gilz | last post by:
Hi I was wondering if anyone could help i have picked this database up from a designer who has now left the company. The code is a couple of years old. On click of a button it open a...
2
by: kirk | last post by:
I'd like to programmatically be able to determine if a network interface is currently in "Local Only" or "Local and Internet" access mode. Also I wasn't sure if this is only applicable to Windows...
3
by: Rahul Babbar | last post by:
Hi, I had the following doubts about the "For Read Only" clause. 1. How does a "for Read only" clause improve the performance? 2. How does a "for Read only" clause compare with "With UR"...
0
by: libish | last post by:
hi all, can any one help me in converting a directory to read only format. and also making a read only directory/file to read/write directory this is to avoid deleting a directory accidently.. ...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.