Yet another "The cursor is READ ONLY."?

Question

My question is different from the others of the kind, because I really need a cursor for rebuilding multiple indices. However, I want to utilise the fact that I am already at the cursor and do an update.

Here is the SQL code:

USE [MyDB]
DECLARE @TableName NVARCHAR(128)
DECLARE @IndexName NVARCHAR(128)
DECLARE @Sql NVARCHAR(MAX)

BEGIN TRY 
    DECLARE c CURSOR LOCAL FORWARD_ONLY KEYSET SCROLL_LOCKS FOR
    SELECT TableName,IndexName
    FROM _NonClusteredIndices_ i
    JOIN _Candidates_ c ON i.ObjectId = c.ObjectId
    WHERE State = 1
    ORDER BY TableName,IndexName
    FOR UPDATE OF i.State
    OPEN c
    FETCH NEXT FROM c INTO @TableName,@IndexName
    WHILE @@FETCH_STATUS = 0
    BEGIN
        SET @Sql = 'ALTER INDEX [' + @IndexName + '] ON [' + @TableName + '] REBUILD'
        EXEC(@Sql)
        UPDATE _NonClusteredIndices_ SET State = 2 WHERE CURRENT OF c
        FETCH NEXT FROM c INTO @TableName,@IndexName
    END
    CLOSE c
    DEALLOCATE c
END TRY
BEGIN CATCH
    SELECT 
        ERROR_NUMBER() AS ErrorNumber,
        ERROR_SEVERITY() AS ErrorSeverity,
        ERROR_STATE() as ErrorState,
        ERROR_PROCEDURE() as ErrorProcedure,
        ERROR_LINE() as ErrorLine,
        ERROR_MESSAGE() as ErrorMessage;
END CATCH

Alas, I get The cursor is READ ONLY

Why? How do I fix it?

EDIT

The DDL of the two tables is:

USE [MyDB]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[_Candidates_](
    [ObjectId] [int] NOT NULL,
    [ClientIdColumnId] [int] NOT NULL,
    [TableName] [nvarchar](128) NOT NULL,
    [ClientIdColumnName] [nvarchar](128) NOT NULL,
    [RowCount] [bigint] NOT NULL
) ON [PRIMARY]

CREATE TABLE [dbo].[_NonClusteredIndices_](
    [ObjectId] [int] NOT NULL,
    [IndexName] [nvarchar](128) NOT NULL,
    [State] [int] NOT NULL DEFAULT ((0))
) ON [PRIMARY]

(Oops, listed a wrong table)

None of the tables have any indices or constraints, except the default constraint for the _NonClusteredIndices_.State column

Answer 1

If one of the tables referenced by the CURSOR has no unique index, the CURSOR will be converted to STATIC. And STATIC cursors are READ-ONLY. See Using Implicit Cursor Conversions for more information.

As an alternative, I suggest you get rid of the CURSOR altogether and do this using a set-based query:

DECLARE @sql NVARCHAR(MAX) = '';

BEGIN TRY
    BEGIN TRANSACTION
        SELECT 
            @sql = @sql + 'ALTER INDEX ' + QUOTENAME(IndexName) + ' ON ' + QUOTENAME(TableName) + ' REBUILD;' + CHAR(10)
        FROM _NonClusteredIndices_ i
        JOIN _Candidates_ c 
            ON i.ObjectId = c.ObjectId
        WHERE State = 1;

        EXEC(@sql);

        UPDATE i
            SET i.State = 2
        FROM _NonClusteredIndices_ i
        JOIN _Candidates_ c 
            ON i.ObjectId = c.ObjectId
        WHERE State = 1;

    COMMIT TRANSACTION
END TRY
BEGIN CATCH
    IF @@TRANCOUNT > 0 BEGIN
        ROLLBACK TRANSACTION
    END
    DECLARE
        @ErrorNumber    INT,
        @ErrorMessage   NVARCHAR(4000),
        @ErrorState     INT,
        @ErrorSeverity  INT,
        @ErrorLine      INT;

    SELECT
        @ErrorNumber    =   ERROR_NUMBER(),
        @ErrorSeverity  =   ERROR_SEVERITY(),
        @ErrorState     =   ERROR_STATE(),
        @ErrorLine      =   ERROR_LINE(),
        @ErrorMessage   =   ERROR_MESSAGE();

    RAISERROR(@ErrorMessage, @ErrorSeverity, @ErrorState);
END CATCH