Post reply

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

  • January 6, 2009 at 9:27 am

    #217183

    Help !!!!

    I run the following code to reindex all my indexes in one go (dont ask why they are all being set to 90 or I will lose the will to live)

    DECLARE @TBL varchar(34),

    @SQLSTR VARCHAR (1000),

    @ctrl CHAR (2)

    SET @ctrl = CHAR (13) + CHAR (10)

    DECLARE TBLCUR CURSOR FOR

    select table_name

    from information_schema.tables

    OPEN TBLCUR

    FETCH NEXT FROM TBLCUR INTO @TBL

    WHILE @@FETCH_STATUS = 0

    BEGIN

    SET @SQLSTR = 'DBCC DBREINDEX ('+char(39)+@TBL+char(39)+','+char(39)+char(39)+',90)'

    print (@SQLSTR)

    exec (@SQLSTR)

    FETCH NEXT FROM TBLCUR INTO @TBL

    END

    CLOSE TBLCUR

    DEALLOCATE TBLCUR

    GO

    and I get

    Server: Msg 16943, Level 16, State 2, Line 25

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

    Having looked around a bit I see it can be caused by autoshrink being on so I've made sure thats turned off etc ... Im the only person in this database and this is my only connection,,,,

    help !

    simon

  • January 6, 2009 at 1:27 pm

    #922126

    Try putting up database in single user mode(if possible) and then run the code. If it runs fine then you need to find what else was bothering when it was in multi user state.

    MJ

Viewing 2 posts - 1 through 2 (of 2 total)

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