Incorrect syntax near 'RESAMPLE'. [SQLSTATE 42000] #422

Question

I am running olahallengren IndexOptimize - USER_DATABASES

Description of the issue

Running job "IndexOptimize - USER_DATABASES" and failing with the following error

UPDATE STATISTICS [DatabaseName].[dbo].[tableName] [indexName] RESAMPLE [SQLSTATE 01000]
Msg 50000, Sev 16, State 1, Line 152 : Msg 102, Incorrect syntax near 'RESAMPLE'. [SQLSTATE 42000]

SQL Server version and edition

Microsoft SQL Server 2019 (RTM-CU6) (KB4563110) - 15.0.4053.23 (X64) Jul 25 2020 11:26:55 Copyright (C) 2019 Microsoft Corporation Enterprise Edition: Core-based Licensing (64-bit) on Windows Server 2019 Datacenter 10.0 (Build 17763: ) (Hypervisor)

Version of the script
Check the header of the stored procedure

cannot determine.
What command are you executing?

EXECUTE dbo.IndexOptimize
@databases = 'DatabaseName',
@FragmentationLow = NULL,
@FragmentationMedium = 'INDEX_REORGANIZE,INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',
@FragmentationHigh = 'INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',
@FragmentationLevel1 = 5,
@FragmentationLevel2 = 15,
@UpdateStatistics = 'ALL',
@OnlyModifiedStatistics = 'Y',
@indexes = 'ALL_INDEXES, -db.Staging.KO_%'

What output are you getting?
Incorrect syntax near 'RESAMPLE'

Answer 1

You have an invalid command. The correct command need WITH before the word RESAMPLE. There are two possibilities:

Somebody modified Ola's procedure and introduced the bug.

The bug was in Ola's procedure from the beginning.

In any case, I suggest you update the procedures by downloading a current version of his maintenance script. If the problem persist, then you need to contact Ola and see if he can fix the bug.

The problem is not in SQL Server.

(I believe that Ola's stuff is on GitHub, so if you are good at T-SQL coding, you can possibly fix this yourself as a code-bransch at GitHub.)

Answer 2

There is logic in the current code to add "WITH" in front of RESAMPLE. Please make sure you are using the most current version of the scripts.

Answer 3

Hi @Kazi, Imran ,

According to this thread, this bug is fixed in the 25 Sep 2016 release. Please try to use the latest version script.

Best Regards,
Amelia

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 4

In my case it was resolved as we have excluded columnstore index