Could not complete cursor operation because the table schema changed after the cursor was declared.

Question

Post reply

Could not complete cursor operation because the table schema changed after the cursor was declared.

APA0876

Hall of Fame

Points: 3853
More actions
September 20, 2012 at 6:57 pm

#264605

Hi,
I had this error:
Could not complete cursor operation because the table schema changed after the cursor was declared.
I was rebuilding indexes based on fragmentation levels that I stored on a table, so I created a cursor that based on that table and I rebuild the indexes, the first night it worked ok and the second night failed with the error above.
I don't what will be the cause, do you have any idea of what could have happened.
Thanks
APA

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

durai nagarajan SSCoach Points: 15778 More actions · Answer 1

can you post the table structure and code you trying to use.

Regards
Durai Nagarajan

shen-dest SSCrazy Points: 2498 More actions · Answer 2

if you declare your cursor as insensitive then you wouldnt get this error.

Regards,
MShenel

APA0876 Hall of Fame Points: 3853 More actions · Answer 3

Hi This is the script, Thanks

ALTER procedure [dbo].[LMCDBATmp_Rebuild_Indexes]

AS

BEGIN

SET NOCOUNT ON;

DECLARE @objectid int;

DECLARE @indexid int;

DECLARE @partitioncount bigint;

DECLARE @schemaname sysname;

DECLARE @objectname sysname;

DECLARE @indexname sysname;

DECLARE @partitionnum bigint;

DECLARE @partitions bigint;

DECLARE @frag float;

DECLARE @command varchar(8000);

--DECLARE partitions CURSOR FOR

-- SELECT objectid,

--indexid,

--partitionnum,

--frag

--FROM LMCDBA_IndextoMaintain

--WHERE UpdatedFlag = 0 AND CONVERT(VARCHAR(8),updatedate, 101) = CONVERT(VARCHAR(8),GETDATE()-1, 101)

--AND OBJECT_NAME(objectid) NOT LIKE 'LMCDBA_IndextoMaintain'

--ORDER BY frag DESC;

DECLARE partitions CURSOR FOR

SELECT objectid,

indexid,

partitionnum,

frag

FROM LMCDBA_IndextoMaintain

WHERE UpdatedFlag = 0

AND OBJECT_NAME(objectid) NOT LIKE 'LMCDBA_IndextoMaintain'

ORDER BY frag DESC;

-- Open the cursor.

OPEN partitions;

-- Loop through the partitions.

FETCH NEXT

FROM partitions

INTO @objectid, @indexid, @partitionnum, @frag;

WHILE @@FETCH_STATUS = 0

BEGIN

SELECT @objectname = o.name, @schemaname = s.name

FROM sys.objects AS o

JOIN sys.schemas as s ON s.schema_id = o.schema_id

WHERE o.object_id = @objectid;

SELECT @indexname = name

FROM sys.indexes

WHERE object_id = @objectid AND index_id = @indexid;

SELECT @partitioncount = count (*)

FROM sys.partitions

WHERE object_id = @objectid AND index_id = @indexid;

-- 30 is an arbitrary decision point at which to switch between reorganizing and rebuilding

IF @frag < 30.0

BEGIN;

SELECT @command = 'ALTER INDEX ' + @indexname + ' ON ' + @schemaname + '.' + @objectname + ' REORGANIZE';

IF @partitioncount > 1

SELECT @command = @command + ' PARTITION=' + CONVERT (CHAR, @partitionnum);

EXEC (@command);

END;

IF @frag >= 30.0

BEGIN;

SELECT @command = 'ALTER INDEX ' + @indexname +' ON ' + @schemaname + '.' + @objectname + ' REBUILD';

IF @partitioncount > 1

SELECT @command = @command + ' PARTITION=' + CONVERT (CHAR, @partitionnum);

EXEC (@command);

END;

DECLARE @UpdateCmm AS VARCHAR(500)

SET @UpdateCmm = 'UPDATE LMCDBA_IndextoMaintain

SET UpdatedFlag = 1

,UpdateDate = GETDATE()

WHERE objectid =' + CAST(@objectid AS VARCHAR(15)) + ' AND indexid = ' + CAST(@indexid AS VARCHAR(15));

EXEC (@UpdateCmm)

PRINT CAST (GETDATE() AS VARCHAR(20)) + ' Executed ' + @command + ' ' + CAST(@frag AS VARCHAR) ;

FETCH NEXT FROM partitions

INTO @objectid, @indexid, @partitionnum, @frag;

END;

CLOSE partitions;

DEALLOCATE partitions;

END

APA0876 Hall of Fame Points: 3853 More actions · Answer 4

Thanks for your responses,

I sent the script so you can confirm if the valid option to avoid this error is declared the cursor insensitve

Thanks

Ant-Green SSC Guru Points: 113507 More actions · Answer 5

Take a look at Ola Hallengren's maintenance scripts (link in my signature) some great maintenance scripts on there including a good index rebuild script.