“The cursor is READ ONLY.The statement has been terminated” problem with UPDATE cursors in SQL Server

I recently got stuck with this problem for seemingly no reason, since my cursor was not declared read-only. I had to spend hours googling around and experimenting for a solution.

The Problem

I use Microsoft Visio to manage my SQL Server database. When you add/edit columns, indices, constraints etc., Visio will generate a script to update the database. Depending on the situation, usually the script generated creates a back-up of the current table, removes all constraints, indices on the table, then drops the table and recreates its. Then puts back the data from backed-up table and finally reinstates the indices, constraints etc.

I had a situation where I had to add an indexed column to a table. I decided to put the data for the existing records for this column using cursor since I had to traverse row-by-row.

I manually inserted the following code in the script file.

-- visio generated script upto create new table before this line
--===========================================================================

-- Migrate old data to newly created table
Set identity_insert "dbo"."SubTicket" on
go

insert into "dbo"."SubTicket" ("SubTicketId", "TicketId", OtherColumnsHere)
select "SubTicketId", "TicketId", OtherColumnsHere from "dbo"."SubTicket_IM0"
go

Set identity_insert "dbo"."SubTicket" off
go

-- update the data in new column for existing records                   

DECLARE @TicketId bigint
DECLARE @TicketIdPrev bigint
DECLARE @SubTicketId bigint
DECLARE @SubTicketNumber int
DECLARE @RowNum int
DECLARE SubTicketList CURSOR LOCAL FOR
SELECT SubTicketId, TicketId, SubTicketNumber
	FROM SubTicket
	ORDER BY TicketId, SubTicketId
    FOR UPDATE OF SubTicketNumber
OPEN SubTicketList

FETCH NEXT FROM SubTicketList INTO @SubTicketId, @TicketId, @SubTicketNumber
WHILE @@FETCH_STATUS <> -1
BEGIN
      IF (@TicketId = @TicketIdPrev) SET @RowNum = @RowNum + 1 ELSE SET @RowNum = 1
      SET @TicketIdPrev = @TicketId
      UPDATE SubTicket SET SubTicketNumber = @RowNum WHERE CURRENT OF SubTicketList
      FETCH NEXT FROM SubTicketList INTO @SubTicketId, @TicketId, @SubTicketNumber
END
CLOSE SubTicketList
DEALLOCATE SubTicketList
go

--===========================================================================
-- visio generated script after create new table (restore constraints etc.) after this line

But after putting this code, trying to run the code causes a series of errors with message

Msg 16929, Level 16, State 1, Line 16

The cursor is READ ONLY.

The statement has been terminated.

The Solution

Found out by collecting information from various online resources and experimenting with the code that unless the table has a primary key defined, the update cursor statement with "WHERE CURRENT OF" won’t work, and will fail with the error mentioned above, irrespective of whether the table contains duplicate records or not.

Once the problem was found, the solution was easy. I moved the Visio generated code that reinstates the primary key on the table above this code, which was otherwise after this.

How this works

If a cursor is cursor opened for update and has a "WHERE CURRENT OF" specified in its UPDATE statement, it won’t work unless the table has a primary key. You may put a primary key column on the table. If that is not possible, use UPDATE statements without the "WHERE CURRENT OF" part, just like any other t-sql UPDATE statement preferably with a WHERE clause.

I hope this tip saves someone’s day.

Advertisements

12 Responses to ““The cursor is READ ONLY.The statement has been terminated” problem with UPDATE cursors in SQL Server”

  1. Martin Munch Says:

    It saved my day. Thank you for the tip!

    /Martin

  2. Peter Royle Says:

    Saved my day, too!
    How ridiculous is that? If it hasn’t got a primary key, does it matter if there are duplicates? If there are, that might be the reason it has not been given a primary key.

  3. GlennS Says:

    Thanks for the tip. Saved my day too!

  4. Elena Says:

    Thanks a lot!!!

  5. Nikolai Shornikov Says:

    opening a cursor for a table with a new “order by” will make it read only; sort the original the way you want the cursor to use it and you don’t need a primary key

  6. Michelline Howl Says:

    Wow. This really saved me a lot of time! Thanks!

  7. Andre Barrozo Says:

    This is the best tip I have been read because I lost more six hours to resolve this problem. I will send to Microsoft Support this bug. Thanks!

  8. Harry Says:

    Great find, two years later it saved my day too. Thanks

  9. Wayne Weeks Says:

    Saved my day!

  10. Moisés Fabro. Says:

    You will go to the sky…! Saved my life, jeje…! TKS.


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: