Incorrect syntax near the keyword 'CROSS'.

Vimalsoft Pty Ltd

Good Day All i have been using the below declared Cursor from the Stored-procedure for around 3 years now. The last couple of weeks i have been getting the error Incorrect syntax near the keyword 'CROSS'. I dont have a Cross Join in this Cursor and this started after Infrastructures upgraded the hardware on the SQL Server , i noticed other settings were lost but was able to recover them.

USE [DBPS]
GO
/****** Object: StoredProcedure [dbo].[DATABASE_MAINTANANCE_PLAN] Script Date: 5/22/2010 12:32:30 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*THIS SP WILL REBUILT ALL THE INDEXES IN ALL TABLES IN ALL DATABASES
THIS WILL BE CALLED FROM AN SSIS PACKAGE. THIS WILL USED IN CLIENTS TO REBUILT INDEXES AND IT WILL BE
RAN EVERY FRIDAY NIGHT
*/

ALTER PROC [dbo].[DATABASE_MAINTANANCE_PLAN]
AS
DECLARE @DatabaseName SYSNAME = DB_NAME(), @TableName VARCHAR(256)
DECLARE @FILLFACTOR INT = 85
DECLARE @DynamicSQL NVARCHAR(max) =
'DECLARE curAllTablesInDB CURSOR FOR SELECT TABLE_SCHEMA +
''.'' + TABLE_NAME AS TABLENAME
FROM ' + @DatabaseName + '.INFORMATION_SCHEMA.TABLES WHERE
TABLE_TYPE = ''BASE TABLE'''

BEGIN
EXEC sp_executeSQL @DynamicSQL -- create tables cursor
OPEN curAllTablesInDB
FETCH NEXT FROM curAllTablesInDB INTO @TableName
WHILE (@@FETCH_STATUS = 0)
BEGIN
SET @DynamicSQL = 'ALTER INDEX ALL ON ' + @TableName +
' REBUILD WITH (FILLFACTOR = ' + CONVERT(VARCHAR,@FILLFACTOR) + ')'
PRINT @DynamicSQL
-- ALTER INDEX ALL ON Purchasing.ShipMethod REBUILD WITH (FILLFACTOR = 85)
EXEC sp_executeSQL @DynamicSQL
FETCH NEXT FROM curAllTablesInDB INTO @TableName
END -- cursor WHILE
CLOSE curAllTablesInDB
DEALLOCATE curAllTablesInDB
END

Thanks

Vuyiswa Maseko, Spoted in Daniweb-- Sorry to rant. I hate websites. They are just wierd. They don't behave like normal code. C#/VB.NET/ASP.NET/SQL7/2000/2005/2008 http://www.vimalsoft.com vuyiswa[at]vimalsoft.com

Vimalsoft Pty Ltd

Thanks I had a table named "CROSS" Cross is a keyword in SQL , that caused a problem for that Dynamic Query. Thanks its resolved now.

Vuyiswa Maseko, Spoted in Daniweb-- Sorry to rant. I hate websites. They are just wierd. They don't behave like normal code. C#/VB.NET/ASP.NET/SQL7/2000/2005/2008 http://www.vimalsoft.com vuyiswa[at]vimalsoft.com

Richard Deeming

Rather than using dynamic SQL and a cursor, why not use the undocumented sp_MSforeachtable procedure[^]?

ALTER PROC [dbo].[DATABASE_MAINTANANCE_PLAN]
AS
BEGIN
DECLARE @command nvarchar(max);
DECLARE @FillFactor int = 85;

SET @command = N'ALTER INDEX ALL ON \[?\] REBUILD WITH (FILLFACTOR = ' + Convert(nvarchar(3), @FillFactor) + N')';
EXEC sp\_MSforeachtable @command;

END

"These people looked deep within my soul and assigned me a number based on the order in which I joined." - Homer