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
bibi92Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

arbiiCommented:
You may have to move these lines out of the while loop -
        EXEC (@command);
        PRINT N''Executed: '' + @command;
Alpesh PatelSolution Architect at TCS (SAFe®Agilist, TOGAF® Foundation, PSM I®, MCP, MCSD)Commented:
EXEC (@command )
bibi92Author Commented:
Done, but it's the same thing.
regards
bibi
Your Guide to Achieving IT Business Success

The IT Service Excellence Tool Kit has best practices to keep your clients happy and business booming. Inside, you’ll find everything you need to increase client satisfaction and retention, become more competitive, and increase your overall success.

arbiiCommented:
The statement works fine for me. Can you post more details.
bibi92Author Commented:
I have no more details, but I see that this script works in another instance.
imran_fastCommented:
change these lines

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

to


EXEC @command;
        PRINT N'Executed:' + @command;
bibi92Author Commented:
Incorrect syntax near ' + @command;
    END;

CLOSE partitions;
DEALLOCATE partitions;

DROP TABLE #work_to_do;

'.
Wouter BoevinkMasterCommented:
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;)?
bibi92Author Commented:
No, same error Msg 102, Level 15, State 1, Line 21
Incorrect syntax near '('.
Wouter BoevinkMasterCommented:
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;
bibi92Author Commented:
Msg 102, Level 15, State 1, Line 21
Incorrect syntax near '('.
Thanks
bibi
Anthony PerkinsCommented:
If you are using SQL Server 2000 or the compatibililty level is set to 80, that script will fail.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
imran_fastCommented:
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
bibi92Author Commented:
Thanks a lot, somebody has restored a db in compatibility 80 in the MSSQL 2008 instance.
Regards
bibi
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.