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