Incorrect syntax near 'Activity' in REBUILD_INDEX SP

Question

Below is the stored procedure used for REBUILD_INDEX of DB tables, It was executing fine before but when I checked now it is showing below error,

Msg 102, Level 15, State 1, Line 3
Incorrect syntax near 'Activity'.

Msg 319, Level 15, State 1, Line 3
Incorrect syntax near the keyword 'with'. If this statement is a common table expression, an xmlnamespaces clause or a change tracking context clause, the previous statement must be terminated with a semicolon

But there's no text 'Activity' in the stored procedure, and no CTE is used. Not able to guess the reason for this sudden issue. Can anyone give me a clue on this?

USE [DBName]
GO

/****** Object:  StoredProcedure [dbo].[REBUILD_INDEX]
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[REBUILD_INDEX]
AS
BEGIN

DECLARE @TableName VARCHAR(255)
DECLARE @sql NVARCHAR(500)
DECLARE @fillfactor INT
SET @fillfactor = 80
DECLARE TableCursor CURSOR FOR
SELECT OBJECT_SCHEMA_NAME([object_id])+'.'+name AS TableName
FROM sys.tables
OPEN TableCursor
FETCH NEXT FROM TableCursor INTO @TableName
WHILE @@FETCH_STATUS = 0
BEGIN
SET @sql = 'ALTER INDEX ALL ON ' + @TableName + ' REBUILD WITH (FILLFACTOR = ' + CONVERT(VARCHAR(3),@fillfactor) + ')'
EXEC (@sql)
FETCH NEXT FROM TableCursor INTO @TableName
END
CLOSE TableCursor
DEALLOCATE TableCursor

END

you should use QUOTENAME for your object names. you would see this with an object name like `some activity` (contains a space) — Martin Smith, Mar 08 '18 at 21:16
Why is table name with Space not accepting in Rebuild Index SP? — Sathiya Kumar V M, Mar 08 '18 at 21:24
why do you think two people have mentioned QUOTENAME to you? have you looked to see what it does? — Martin Smith, Mar 08 '18 at 21:25
@MartinSmith Your question made me to think of it. I searched and found QUOTENAME is making objectname as [objectname] hence it will fix the issue even if we have table name with Space. Thanks Sir. — Sathiya Kumar V M, Mar 08 '18 at 21:32

Lukasz Szozda · Accepted Answer · 2018-03-08T21:11:38.050

1

You should add semicolons and use QUOTENAME:

ALTER PROCEDURE [dbo].[REBUILD_INDEX]
AS
BEGIN

DECLARE @TableName VARCHAR(255);
DECLARE @sql NVARCHAR(500);
DECLARE @fillfactor INT;
SET @fillfactor = 80;
DECLARE TableCursor CURSOR FOR
SELECT OBJECT_SCHEMA_NAME([object_id])+'.'+QUOTENAME(name) AS TableName
FROM sys.tables;
OPEN TableCursor;
FETCH NEXT FROM TableCursor INTO @TableName;
WHILE @@FETCH_STATUS = 0
BEGIN
SET @sql = 'ALTER INDEX ALL ON ' + @TableName 
   + ' REBUILD WITH (FILLFACTOR = ' + CONVERT(VARCHAR(3),@fillfactor) + ');';

PRINT @sql; -- debug only
EXEC (@sql);

FETCH NEXT FROM TableCursor INTO @TableName;
END;
CLOSE TableCursor;
DEALLOCATE TableCursor;

END;

I bet parser mistakes WITH Common Table Expression with WITH from ALTER INDEX.

Anyway I would use Ola Hallengren's SQL Server Index and Statistics Maintenance Scripts instead of reinventing the wheel.

edited Mar 08 '18 at 21:11

answered Mar 08 '18 at 21:06

Lukasz Szozda

162,964
23
234
275

`WHILE @@FETCH_STATUS = 0;` in this line, i'm facing `Msg 102, Level 15, State 1, Line 10 Incorrect syntax near ';'.` – Sathiya Kumar V M Mar 08 '18 at 21:11
@SathiyaKumar Just remove that `;` – Lukasz Szozda Mar 08 '18 at 21:12
Your query helped me to found the place where issue occurred and QUOTENAME helped me to fix the issue. :) – Sathiya Kumar V M Mar 08 '18 at 21:33

score 0 · Answer 2 · answered Mar 08 '18 at 21:48

0

The "Activity" is almost certainly part of a Table Name with a space in it. Add Quotename(..) around the @TableName in the ALTER INDEX statement.

answered Mar 08 '18 at 21:48

Sean Devoy

66
5

Incorrect syntax near 'Activity' in REBUILD_INDEX SP

2 Answers2