Automatic Deployment Rule failing with error 0x87d20003

Recently, one of our ADRs has been failing with error code 0x87d20003 and description “SQl Error”:

The ruleengine.log shows:

GetTargetCollectionID failed with error:-2016280573

-2016280573 in decimal translates to 0x87d20003.

Using the information in https://www.reddit.com/r/SCCM/comments/6zverb/deleting_collection_used_in_adr_breaks_deployments/, I wrote the following to show what Collection is missing:

declare @ADRName varchar (200) = 'Auto Monthly Patches'
select
   Data.value('(/DeploymentCreationActionXML//CollectionId/node())[1]', 'nvarchar(max)') CollectionID
 , c.CollectionID
 , c.Name
from RuleActions ra
left join v_Collection c on c.CollectionID = Data.value('(/DeploymentCreationActionXML//CollectionId/node())[1]', 'nvarchar(max)')
where ra.RuleID = (select AutoDeploymentID from vSMS_AutoDeployments where Name = @ADRName)
and ra.Name = 'DeploymentTemplate'

The NULL value shows the missing collection ID.

The only supported way to fix this is to delete the ADR and create anew.