“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.