473,390 Members | 1,651 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,390 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 13384

"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: taylorcarr | last post by:
A Canon printer is a smart device known for being advanced, efficient, and reliable. It is designed for home, office, and hybrid workspace use and can also be used for a variety of purposes. However,...
0
by: ryjfgjl | last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
0
by: ryjfgjl | last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
1
by: nemocccc | last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
0
by: Hystou | last post by:
There are some requirements for setting up RAID: 1. The motherboard and BIOS support RAID configuration. 2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
0
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
0
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers,...
0
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
0
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows...

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.