Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Error when @Indexes contains an object with a period in the name #766

Open
DatabaseJase opened this issue Sep 18, 2023 · 0 comments
Open

Comments

@DatabaseJase
Copy link

Description of the issue
Index Maintenance script exits with an error if @indexes parameter specifies an object that contains a full stop/period (.) which while bad practice is not an illegal name as it can be used to create tables/indexes. The error is not raised if the @indexes parameter is changed to 'ALL_INDEXES' (even though the index is included) but in this use case we want to specify the actual index name.

CREATE TABLE [dbo].[TestTable](
[ID] [int] IDENTITY(1,1) NOT NULL,
[TestColumn] [smallint] NULL,
);
GO

CREATE NONCLUSTERED INDEX [IX_dbo.TestTable_TestColumn] ON [dbo].[TestTable] ([TestColumn] ASC);
GO

I have identified the code that causes the error and it is this:

Indexes4 (DatabaseName, SchemaName, ObjectName, IndexName, StartPosition, Selected) AS
(
SELECT CASE WHEN PARSENAME(IndexItem,4) IS NULL THEN PARSENAME(IndexItem,3) ELSE PARSENAME(IndexItem,4) END AS DatabaseName,
CASE WHEN PARSENAME(IndexItem,4) IS NULL THEN PARSENAME(IndexItem,2) ELSE PARSENAME(IndexItem,3) END AS SchemaName,
CASE WHEN PARSENAME(IndexItem,4) IS NULL THEN PARSENAME(IndexItem,1) ELSE PARSENAME(IndexItem,2) END AS ObjectName,
CASE WHEN PARSENAME(IndexItem,4) IS NULL THEN '%' ELSE PARSENAME(IndexItem,1) END AS IndexName,
StartPosition,
Selected
FROM Indexes3
)

SQL Server version and edition
Microsoft SQL Server 2019 (RTM-CU20) (KB5024276) - 15.0.4312.2 (X64) Apr 1 2023 12:10:46 Copyright (C) 2019 Microsoft Corporation Developer Edition (64-bit) on Windows 10 Enterprise 10.0 (Build 22621: ) (Hypervisor)

Version of the script
Version: 2022-12-03 17:23:44

What command are you executing?
EXECUTE [DBA].[dbo].[IndexOptimize]
@databases = 'DataMasking'
,@indexes = 'DataMasking.dbo.TestTable.IX_dbo.TestTable_TestColumn'
,@UpdateStatistics = 'ALL'
,@LogToTable = 'N' -- Y = Log commands to the table / N = Do not log commands to the table
,@execute = 'Y' -- Y = Execute commands / N = Only print commands
GO

What output are you getting?
Date and time: 2023-09-18 09:17:46
Server: CPC-jason-A59JZ\SQL2019
Version: 15.0.4312.2
Edition: Developer Edition (64-bit)
Platform: Windows
Procedure: [DBA].[dbo].[IndexOptimize]
Parameters: @databases = 'DataMasking', @FragmentationLow = NULL, @FragmentationMedium = 'INDEX_REORGANIZE,INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE', @FragmentationHigh = 'INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE', @FragmentationLevel1 = 5, @FragmentationLevel2 = 30, @MinNumberOfPages = 1000, @MaxNumberOfPages = NULL, @SortInTempdb = 'N', @MaxDOP = NULL, @FillFactor = NULL, @PadIndex = NULL, @LOBCompaction = 'Y', @UpdateStatistics = 'ALL', @OnlyModifiedStatistics = 'N', @StatisticsModificationLevel = NULL, @StatisticsSample = NULL, @StatisticsResample = 'N', @PartitionLevel = 'Y', @MSShippedObjects = 'N', @indexes = 'DataMasking.dbo.TestTable.IX_dbo.TestTable_TestColumn', @Timelimit = NULL, @delay = NULL, @WaitAtLowPriorityMaxDuration = NULL, @WaitAtLowPriorityAbortAfterWait = NULL, @Resumable = 'N', @AvailabilityGroups = NULL, @LockTimeout = NULL, @LockMessageSeverity = 16, @StringDelimiter = ',', @DatabaseOrder = NULL, @DatabasesInParallel = 'N', @ExecuteAsUser = NULL, @LogToTable = 'N', @execute = 'Y'
Version: 2022-12-03 17:23:44
Source: https://ola.hallengren.com

Msg 50000, Level 16, State 1, Procedure DBA.dbo.IndexOptimize, Line 1181 [Batch Start Line 14]
The value for the parameter @indexes is not supported.

Msg 50000, Level 16, State 1, Procedure DBA.dbo.IndexOptimize, Line 1181 [Batch Start Line 14]
The documentation is available at https://ola.hallengren.com/sql-server-index-and-statistics-maintenance.html.

Date and time: 2023-09-18 09:17:46

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
None yet
Projects
None yet
Development

No branches or pull requests

1 participant