Link to home
Start Free TrialLog in
Avatar of bibi92
bibi92Flag for France

asked on

error on script rebuild index

Hello,
With this following script, There is an error on execution :

Msg 102, Level 15, State 1, Line 21
Incorrect syntax near '('.
Executed: ALTER INDEX [IX_STADE_PERIODE_UNIQUE] ON [dbo].[STADE_AFFAIRE_PERIODE] REORGANIZE

DECLARE @command nvarchar(Max)

SELECT @command = 'USE [?]
SET NOCOUNT ON;
SET QUOTED_IDENTIFIER ON;
DECLARE @objectid int;
DECLARE @indexid int;
DECLARE @partitioncount bigint;
DECLARE @schemaname nvarchar(130);
DECLARE @objectname nvarchar(130);
DECLARE @indexname nvarchar(130);
DECLARE @partitionnum bigint;
DECLARE @partitions bigint;
DECLARE @frag float;
DECLARE @command nvarchar(4000);

SELECT
    object_id AS objectid,
    index_id AS indexid,
    partition_number AS partitionnum,
    avg_fragmentation_in_percent AS frag
INTO #work_to_do
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL , NULL, ''LIMITED'')
WHERE avg_fragmentation_in_percent > 10.0 AND index_id > 0;

DECLARE partitions CURSOR FOR SELECT * FROM #work_to_do;

OPEN partitions;

WHILE (1=1)
    BEGIN;
        FETCH NEXT
           FROM partitions
           INTO @objectid, @indexid, @partitionnum, @frag;
        IF @@FETCH_STATUS < 0 BREAK;
        SELECT @objectname = QUOTENAME(o.name), @schemaname = QUOTENAME(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 = QUOTENAME(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;

        IF @frag < 30.0
            SET @command = N''ALTER INDEX '' + @indexname + N'' ON '' + @schemaname + N''.'' + @objectname + N'' REORGANIZE'';
        IF @frag >= 30.0
            SET @command = N''ALTER INDEX '' + @indexname + N'' ON '' + @schemaname + N''.'' + @objectname + N'' REBUILD'';
        IF @partitioncount > 1
            SET @command = @command + N'' PARTITION='' + CAST(@partitionnum AS nvarchar(10));
        EXEC (@command);
        PRINT N''Executed: '' + @command;
    END;

CLOSE partitions;
DEALLOCATE partitions;

DROP TABLE #work_to_do;

'

print @command
EXEC sp_MSforeachdb @command

Thanks
Regards
bibi
Avatar of arbii
arbii

You may have to move these lines out of the while loop -
        EXEC (@command);
        PRINT N''Executed: '' + @command;
Avatar of Alpesh Patel
EXEC (@command )
Avatar of bibi92

ASKER

Done, but it's the same thing.
regards
bibi
The statement works fine for me. Can you post more details.
Avatar of bibi92

ASKER

I have no more details, but I see that this script works in another instance.
change these lines

EXEC (@command);
        PRINT N''Executed: '' + @command;

to


EXEC @command;
        PRINT N'Executed:' + @command;
Avatar of bibi92

ASKER

Incorrect syntax near ' + @command;
    END;

CLOSE partitions;
DEALLOCATE partitions;

DROP TABLE #work_to_do;

'.
Getting the exec out of the while loop won't give you the result you'd expect.
Only the last result from the while loop will be executed.

If you remove the EXEC(@command) does it print out all the command (PRINT N''Executed: '' + @command;)?
Avatar of bibi92

ASKER

No, same error Msg 102, Level 15, State 1, Line 21
Incorrect syntax near '('.
I guess line 21 is one of the below?
First comment out  EXEC(@command) and then comment out the lines below till you don't get an error, so we can pinpoint the problem.

SELECT @objectname = QUOTENAME(o.name), @schemaname = QUOTENAME(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 = QUOTENAME(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;
Avatar of bibi92

ASKER

Msg 102, Level 15, State 1, Line 21
Incorrect syntax near '('.
Thanks
bibi
ASKER CERTIFIED SOLUTION
Avatar of Anthony Perkins
Anthony Perkins
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
for sql 2000 databases you will get error on the following statement

FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL , NULL, 'LIMITED')


as this feature is introduced in sql 2005
Avatar of bibi92

ASKER

Thanks a lot, somebody has restored a db in compatibility 80 in the MSSQL 2008 instance.
Regards
bibi