SQLTeam.com | Weblogs | Forums

Msg 102, Level 15, State 1, Line 6 Incorrect syntax near ')'

I am getting the error when executing in SQL 2008 the following procedure:

USE [DB]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[spIndexfragmentationCheck] (
@CheckFrag BIT --1 Show Output, 0 Fix Fragmentation
)

AS

SET NOCOUNT ON

DECLARE @cmd NVARCHAR(1000),
@TableName VARCHAR(255),
@DatabaseName VARCHAR(255),
@SchemaName VARCHAR(255),
@IndexName VARCHAR(255),
@AvgFragmentationInPercent DECIMAL,
@FillFactor INT,
@FragmentationThresholdForReorganizeTableLowerLimit VARCHAR(10),
@FragmentationThresholdForRebuildTableLowerLimit VARCHAR(10),
@dbName VARCHAR(50),
@pageLocks BIT

SET @FillFactor = 90 --Set for Query Optimization, DO NOT EDIT
SET @FragmentationThresholdForReorganizeTableLowerLimit = '10.0' --Percent
SET @FragmentationThresholdForRebuildTableLowerLimit = '35.0' --Percent

--Make sure temp table is deleted
IF (SELECT OBJECT_ID('tempdb..#Index_Frag')) IS NOT NULL
DROP TABLE #Index_Frag

DECLARE Roy CURSOR LOCAL FAST_FORWARD FOR
SELECT name FROM master..sysdatabases
WHERE Name NOT IN ('tempdb', 'master','msdb','model')
AND (version IS NULL OR version > 0)

OPEN Roy
FETCH NEXT FROM Roy INTO @dbName

WHILE @@FETCH_STATUS = 0
BEGIN
IF @CheckFrag = 0
BEGIN
SET @cmd = 'USE '+@dbName+' INSERT INTO DB..IndexFragmentation
SELECT OBJECT_NAME(IPS.OBJECT_ID) AS [TableName], avg_fragmentation_in_percent AS [PercentFragmented], SI.name [IndexName], '''+@dbName+''' AS [DatabaseName],
schema_name(ST.schema_id) AS [SchemaName], 0 AS IsProcessed, GETDATE(), NULL, si.allow_page_locks AS Page_Lock_Enabled
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, NULL) IPS
JOIN sys.tables ST WITH (NOLOCK) ON IPS.OBJECT_ID = ST.OBJECT_ID
JOIN sys.indexes SI WITH (NOLOCK) ON IPS.OBJECT_ID = SI.OBJECT_ID AND IPS.index_id = SI.index_id
WHERE ST.is_ms_shipped = 0 AND SI.name IS NOT NULL
AND avg_fragmentation_in_percent >= CONVERT(DECIMAL, '+@FragmentationThresholdForReorganizeTableLowerLimit+')'
END
ELSE
BEGIN
SET @cmd = 'USE '+@dbName+'
SELECT OBJECT_NAME(IPS.OBJECT_ID) AS [TableName], avg_fragmentation_in_percent AS [PercentFragmented], SI.name [IndexName], '''+@dbName+''' AS [DatabaseName],
schema_name(ST.schema_id) AS [SchemaName], 0 AS IsProcessed, GETDATE(), NULL, si.allow_page_locks AS Page_Lock_Enabled
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, NULL) IPS
JOIN sys.tables ST WITH (NOLOCK) ON IPS.OBJECT_ID = ST.OBJECT_ID
JOIN sys.indexes SI WITH (NOLOCK) ON IPS.OBJECT_ID = SI.OBJECT_ID AND IPS.index_id = SI.index_id
WHERE ST.is_ms_shipped = 0 AND SI.name IS NOT NULL
AND avg_fragmentation_in_percent >= CONVERT(DECIMAL, '+@FragmentationThresholdForReorganizeTableLowerLimit+')
ORDER BY avg_fragmentation_in_percent DESC'
END

EXEC (@cmd)

FETCH NEXT FROM Roy INTO @dbName

END

CLOSE Roy
DEALLOCATE Roy

BEGIN TRY
DECLARE Roy CURSOR FAST_FORWARD FOR
SELECT TableName, PercentFragmented, DatabaseName, SchemaName, IndexName, AllowPageLocks FROM DB..IndexFragmentation
WHERE IsProcessed = 0

OPEN Roy
FETCH NEXT FROM Roy INTO @TableName, @AvgFragmentationInPercent, @DatabaseName, @SchemaName, @IndexName, @pageLocks

WHILE @@FETCH_STATUS = 0
BEGIN
	PRINT 'WORKING ON INDEX: ' + @IndexName + ' ON TABLE ' + RTRIM(LTRIM(@DatabaseName)) + '.' + RTRIM(LTRIM(@SchemaName)) + '.' + RTRIM(LTRIM(@TableName))
	
	--ENABLE PAGE LOCKS
	SET @cmd = 'ALTER INDEX ' + @IndexName + ' ON ' + RTRIM(LTRIM(@DatabaseName)) + '.' + RTRIM(LTRIM(@SchemaName)) + '.' + RTRIM(LTRIM(@TableName)) + ' SET (ALLOW_PAGE_LOCKS = ON);'
	EXEC (@cmd)
	
	--REORGANIZE INDEX
	IF((@AvgFragmentationInPercent >= @FragmentationThresholdForReorganizeTableLowerLimit) AND (@AvgFragmentationInPercent < @FragmentationThresholdForRebuildTableLowerLimit))
	BEGIN
		SET @cmd = 'ALTER INDEX ' + @IndexName + ' ON ' + RTRIM(LTRIM(@DatabaseName)) + '.' + RTRIM(LTRIM(@SchemaName)) + '.' + RTRIM(LTRIM(@TableName)) + ' REORGANIZE' 
		EXEC (@cmd)
	END
	--REBUILD INDEX
	ELSE IF (@AvgFragmentationInPercent >= @FragmentationThresholdForRebuildTableLowerLimit)
	BEGIN
		SET @cmd = 'ALTER INDEX ' + @IndexName + ' ON ' + RTRIM(LTRIM(@DatabaseName)) + '.' + RTRIM(LTRIM(@SchemaName)) + '.' + RTRIM(LTRIM(@TableName)) + ' REBUILD WITH (FILLFACTOR = ' + CONVERT(VARCHAR(3),@FillFactor) + ', STATISTICS_NORECOMPUTE = OFF)' 
		EXEC (@cmd)
	END
	
	--CHECK IF PAGE LOCKS WERE DISABLED BEFORE
	IF @pageLocks = 0
	BEGIN
		SET @cmd = 'ALTER INDEX ' + @IndexName + ' ON ' + RTRIM(LTRIM(@DatabaseName)) + '.' + RTRIM(LTRIM(@SchemaName)) + '.' + RTRIM(LTRIM(@TableName)) + ' SET (ALLOW_PAGE_LOCKS = OFF);'
		EXEC (@cmd)
	END

	--UPDATE THE TABLE TO REFLECT THAT THE INDEX WAS PROCESSED SUCCESSFULLY
	UPDATE DB..IndexFragmentation SET IsProcessed = 1, ProcessedDateTime = GETDATE()
	WHERE TableName = @TableName AND IndexName = @IndexName AND CONVERT(VARCHAR(10), Added,110) = CONVERT(VARCHAR(10), GETDATE(), 110)

	FETCH NEXT FROM Roy INTO @TableName, @AvgFragmentationInPercent, @DatabaseName, @SchemaName, @IndexName, @pageLocks
END

CLOSE Roy
DEALLOCATE Roy

END TRY
BEGIN CATCH
PRINT 'DATE: ' + CONVERT(VARCHAR, GETDATE()) + ' There is some run time exception.'
PRINT 'ERROR CODE: ' + CONVERT(VARCHAR, ERROR_NUMBER())
PRINT 'ERROR MESSAGE: ' + ERROR_MESSAGE()
END CATCH

Hi I tried to create the procedure

It created and did not give any error