bibi92
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_PERIO DE] 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_perce nt AS frag
INTO #work_to_do
FROM sys.dm_db_index_physical_s tats (DB_ID(), NULL, NULL , NULL, ''LIMITED'')
WHERE avg_fragmentation_in_perce nt > 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
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_PERIO
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_perce
INTO #work_to_do
FROM sys.dm_db_index_physical_s
WHERE avg_fragmentation_in_perce
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
EXEC (@command )
ASKER
Done, but it's the same thing.
regards
bibi
regards
bibi
The statement works fine for me. Can you post more details.
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;
EXEC (@command);
PRINT N''Executed: '' + @command;
to
EXEC @command;
PRINT N'Executed:' + @command;
ASKER
Incorrect syntax near ' + @command;
END;
CLOSE partitions;
DEALLOCATE partitions;
DROP TABLE #work_to_do;
'.
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;)?
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;)?
ASKER
No, same error Msg 102, Level 15, State 1, Line 21
Incorrect syntax near '('.
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;
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;
ASKER
Msg 102, Level 15, State 1, Line 21
Incorrect syntax near '('.
Thanks
bibi
Incorrect syntax near '('.
Thanks
bibi
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
for sql 2000 databases you will get error on the following statement
FROM sys.dm_db_index_physical_s tats (DB_ID(), NULL, NULL , NULL, 'LIMITED')
as this feature is introduced in sql 2005
FROM sys.dm_db_index_physical_s
as this feature is introduced in sql 2005
ASKER
Thanks a lot, somebody has restored a db in compatibility 80 in the MSSQL 2008 instance.
Regards
bibi
Regards
bibi
EXEC (@command);
PRINT N''Executed: '' + @command;