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
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

  • 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 '17 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 '17 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 '17 at 15:42

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.