Try our conversational search powered by Generative AI!

Maintain Database Indexes job fails if custom schemas used

Found in

EPiServer.Commerce 12.16.0

Fixed in

EPiServer.Commerce 13.3.1

(Or a related package)

Created

May 09, 2019

Updated

Jun 07, 2019

State

Closed, Fixed and tested


Description

Steps to reproduce:
1. Create a table and indexes with a different schema like this example:

USE [XXXDatabasenameXXX]
GO
/****** Object:  Schema [storefront]    Script Date: 2019-05-09 11:09:50 ******/
CREATE SCHEMA [storefront]
GO 
 
/****** Object:  Table [storefront].[ModuleSet]    Script Date: 2019-05-09 11:08:17 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [storefront].[ModuleSet](
                [Id] [int] IDENTITY(1,1) NOT NULL,
                [Group] [nvarchar](255) NOT NULL,
                [Ready] [bit] NOT NULL,
CONSTRAINT [PK_Storefront_ModuleSet_Id] PRIMARY KEY CLUSTERED
(
                [Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE TABLE [storefront].[StagingDocuments](
                [Id] [int] IDENTITY(1,1) NOT NULL,
                [Key] [nvarchar](255) NOT NULL,
                [Data] [nvarchar](max) NOT NULL,
                [Type] [int] NOT NULL,
                [StoreId] [nvarchar](255) NOT NULL,
                [Group] [nvarchar](255) NULL,
                [Set] [int] NOT NULL,
                [CreatedUtc] [datetime2](7) NOT NULL,
CONSTRAINT [PK_Storefront_StagingDocuments_Id] PRIMARY KEY CLUSTERED
(
                [Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
/****** Object:  Index [IDX_Storefront_StagingDocuments_Type_StoreId]    Script Date: 2019-05-09 11:17:47 ******/
CREATE NONCLUSTERED INDEX [IDX_Storefront_StagingDocuments_Type_StoreId] ON [storefront].[StagingDocuments]
(
                [Type] ASC,
                [StoreId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO

2. Add <add key="episerver::LowFragmentationThreshold" value ="-1"/> to your appSettings.
3. Run the "Maintain Database Indexes" scheduled job.

Expected:
Should rebuild indexes

Actual:
Get error when running job. "Cannot find the object" ModuleSet "because it does not exist or you do not have permissions."