Orchestrator Upgrade Fails With System.Exception: Migration Failed: Cannot Drop The Index 'dbo.vwQueueReferences.IX_vwQueueReferences', Because It Does Not Exist Or You Do Not Have Permission

News Knowledge Base
,
1

Orchestrator upgrade from 2020.10.4 to 2021.10.4, throws error " Cannot drop the index because it does not exist or you do not have permission."

An issue can occur during upgrade paths that may cause indexes to be removed. (Assumption based off similar issues in past. Cause is still unknown) .

  1. First validate that the Index truly does not exist,
  1. Open SSMC, and go to Orchestrator DB>Views>dbo.vwQueueReferences>Indexes

  1. If it does exist, check permissions. If it does not continue with following.

  1. In order to solve this particular index is missing we need to recreate index before upgrade.

  1. The query to create the missing index is as follows,

CREATE VIEW [dbo].[vwQueueReferences] WITH SCHEMABINDING AS

SELECT qd.Id as QueueDefinitionId, qi.Reference as Reference

FROM dbo.QueueDefinitions qd

INNER JOIN dbo.QueueItems qi ON qd.Id = qi.QueueDefinitionId

WHERE qd.IsDeleted = 0

AND qd.EnforceUniqueReference = 1

AND qi.Reference IS NOT NULL

AND qi.RetryNumber = 0

and qi.Status != 6 -- Check for deleted items

GO

CREATE UNIQUE CLUSTERED INDEX [IX_vwQueueReferences] ON [dbo].[vwQueueReferences](QueueDefinitionId, Reference)

GO