July 3, 2019 at 1:04 pm
Hi i cannot find the error in the following code: when executing is showing:
Msg 102, Level 15, State 1, Line 6
Incorrect syntax near ')'.
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
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 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
July 3, 2019 at 1:12 pm
That codes parses fine for me on SQL 2016.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
July 3, 2019 at 1:18 pm
same for me, just here it is not working sql 2008 express version. I dont understand why ?
July 3, 2019 at 1:28 pm
it is because the database where is doing the fragmentation has "_" on its name like DB_Data, how to solve this ??
July 3, 2019 at 2:13 pm
To be safe I would put the database name, schema name, and table name inside the QUOTENAME() function.
July 3, 2019 at 2:37 pm
The problem is very likely in your dynamic SQL. I wouldn't be surprised if it's because you aren't properly quoting and parametrising your queries (as the error is on line 6). Take this simple example below:
DECLARE @SQL nvarchar(MAX);
SET @SQL = 'SELECT 1 AS one UNION SELECT ''a'' as one;';
EXEC sp_executesql @SQL;
The error message
Rather than explaining here, have a look at this article I wrote a little ago; which shows how to parametrise and quote your values properly. If you're still getting the error then, then you're likely want to inspect the values of your dynamic statements using PRINT/SELECT.
Apparently I didn't click submit when I wrote this about 3 hours an hour ago.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply