Solved

Cursor read-Only Error

Posted on 2011-02-28
3
695 Views
Last Modified: 2012-05-11
     
The following code gives an error of "cursor is read only".
The problem is caused by the order by clause of the Cursor select definition.
Is there a fix for this scenario.
Typically the table will contain records with Batch values of 100,120, 139, 200 ..etc and I wish to
update the Batch values as 10,20,30,40 ..etc  


                declare @n smallint
      declare @batch smallint
      DECLARE cursor1 CURSOR SCROLL_LOCKS
      FOR
      SELECT     Batch
      FROM       JazzCarryOver
      order by Batch FOR UPDATE OF Batch;

      OPEN cursor1;

        set @n = 10
      FETCH NEXT FROM Cursor1 into @batch
-- re number Batch in ascending order
      WHILE @@FETCH_STATUS = 0
      BEGIN
            UPDATE JazzCarryOver
            SET Batch = @n
            WHERE CURRENT OF cursor1;
            set @n = @n + 10
            FETCH NEXT FROM Cursor1 into @lotnum,@batch
    END;
      CLOSE Cursor1;
      DEALLOCATE Cursor1;

0
Comment
Question by:alcindor
3 Comments
 
LVL 12

Assisted Solution

by:HugoHiasl
HugoHiasl earned 100 total points
ID: 35002369
updating a row in a cursor where multiple rows are aggregated by using a group by clause is not possible.

What would you expect the update to do? Updating ALL relevant rows or updating only the FIRST or LAST? Or one in the middle?

0
 
LVL 75

Accepted Solution

by:
Aneesh Retnakaran earned 400 total points
ID: 35002420
you dont really need a cursor here , use the following

declare @n int = 0
update JazzCarryOver
set @n = @n+10,
   batch = @n
0
 
LVL 2

Author Closing Comment

by:alcindor
ID: 35003237
Thanks guys,
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

830 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question