1

One of my client is having issues with one of the jobs failing every weekend to perform index maintenance on a couple of databases. It is a SQL job which uses maintenance plans underneath. Okay, so the maintenance plans consist of check database integrity task, followed by 3 t-sql task in sequence which runs the same script but for different databases (code provided below) and finally runs a reorg task and then update of statistics. I know this seems a bit weird the way they are running things at this time but I will change it moving forward. Presently, I am stuck with this job completing the maintenance check but failing to execute the t-sql task with the following error.

Executing the query "USE XMain;

declare @frag_Temp as Table ( ..." failed with the following error: "Incorrect syntax near '-'. Changed database context to 'XMain'.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

USE XMain;

DECLARE @frag_Temp AS TABLE (
  ID int IDENTITY (1, 1),
  [objectid] [int] NULL,
  [indexid] [int] NULL,
  [partitionnum] [int] NULL,
  [frag] [float] NULL
)

DECLARE @Count int
DECLARE @i tinyint = 1
DECLARE @schemaname sysname;
DECLARE @objectname sysname;
DECLARE @indexname sysname;
DECLARE @objectid int;
DECLARE @indexid int;
DECLARE @partitionnum bigint;
DECLARE @partitioncount bigint;
DECLARE @SQLCommand AS nvarchar(3000)
INSERT INTO @frag_Temp
  SELECT
    object_id AS objectid,
    index_id AS indexid,
    partition_number AS partitionnum,
    avg_fragmentation_in_percent AS frag
  FROM sys.DM_DB_INDEX_PHYSICAL_STATS(DB_ID(), NULL, NULL, NULL, 'LIMITED')
  WHERE avg_fragmentation_in_percent >= 30.0
  AND index_id > 0;

-- Get Total Count
SELECT
  @Count = COUNT(*)
FROM @frag_Temp
WHILE (@i <= @Count)
BEGIN
  SELECT
    @objectid = objectid,
    @indexid = indexid,
    @partitionnum = partitionnum
  FROM @frag_Temp
  WHERE ID = @i

  -- Get tableName and its schema
  SELECT
    @objectname = o.name,
    @schemaname = c.name
  FROM sys.objects o
  INNER JOIN sys.schemas c
    ON o.schema_ID = c.schema_ID
  WHERE o.object_id = @objectid
  -- Get Index Name
  SELECT
    @indexname = name
  FROM sys.indexes
  WHERE index_id = @indexid
  AND object_id = @objectid
  -- Get Partition Count
  SELECT
    @partitioncount = COUNT(*)
  FROM sys.partitions
  WHERE object_id = @objectid
  AND index_id = @indexid

  SELECT
    @SQLCommand = 'Alter Index ' + @indexname + ' ON ' + @schemaname + '.' + @objectname + ' REBUILD;'
  IF (@partitioncount > 1)
    SELECT
      @SQLCommand = @SQLCommand + ' PARTITION=' + CONVERT(char, @partitionnum);

  EXEC (@SQLCommand);

  -- Increment Count
  SET @i = @i + 1

END
GO

1 Answer 1

0

Your client should use the QUOTENAME function, if not, here is an example:

DECLARE @schemaname sysname = 'dbo';
DECLARE @objectname sysname='-mySuperTableName';
DECLARE @indexname sysname = 'ix1';
DECLARE @SQLCommand AS nvarchar(3000)

  SELECT
    @SQLCommand = 'Alter Index ' + @indexname + ' ON ' + @schemaname + '.' + @objectname + ' REBUILD;'
    print  @SQLCommand 

Try to execute this code and the command you received is Alter Index ix1 ON dbo.-mySuperTableName REBUILD; and it definitely gives you an error

Incorrect syntax near '-'.

if you try to execute the command

3
  • I do not want to disagree. However, the same code ran perfectly fine without any issues when I ran it through a separate maintenance plan just the 3 tasks and it completed without any issues...so not sure if it is as what you've explained.
    – Feivel
    Jul 10, 2017 at 8:59
  • Your code is parsed perfectly so it has no syntax errors; If you search for '-' in it the only text that contains '-' is comment. To exclude the comments you can use /*...*/ and try to execute again, if the error is the same and the new code has no '-' at all the only thing is the dynamic code, and in your dynamic code there is no '-', so the only thing that can bring '-' in your code is some table name
    – sepupic
    Jul 10, 2017 at 9:15
  • Is it possible that the client created an index or table with a name that is causing the errors? While it would be a big coincidence for a change in the job to happen to coincide with an index/table name being added that's causing a problem, it's not impossible. You could add some code to log the value of @SQLCommand before it's executed, so you can confirm that there are no problems there.
    – RDFozz
    Jul 10, 2017 at 15:42

This site is temporarily in read-only mode and not accepting new answers.

Not the answer you're looking for? Browse other questions tagged .