Pre-Production Client Deployment Collection - I deleted it - now i have SQL errors in policypv.log

Question

After successful upgrade to Current Branch, and a successful client update I deleted the collection where the Pre-Prod Client deployment was targeted at. (I was tidying up)

Since then i have been getting SQL errors in policypv.log because the collection does not exist, and i am unable to assign a new collection in the site hierarchy settings.

I was wondering if anyone had the experience to help me resolve /work around this.

Here is a snippet of the log.

The ID of the collection i deleted is referenced as "TST003E1"

PolicyID CLIPIL00 PolicyVersion 65.00
Notifying policy provider about changes in policy content/targeting
Successfully updated Policy for CLIPIL0
UpdateClientPilotingPolicy: detected piloting targeted collection is changed from '' to 'TST003E1'.
Policy or Policy Target Change Event triggered

*** declare @pti table (Col int primary key, AutoTarget bit, Predeploy bit)~; with PTI(Col, AutoTarget, Predeploy) as (select 0, 1, 0)~insert @pti select Col, max(AutoTarget), max(Predeploy) from PTI group by Col~declare @padbid int; set @padbid=16810068~declare @CNT int; set @CNT =0~update pcm set AutoTarget=pti.AutoTarget,~ Predeploy=pti.Predeploy, IsTargetingChanged=1, IsTombstoned=0~ from @pti pti join PolicyCollMap pcm on pcm.PADBID=@padbid and pcm.TargetCollectionID=pti.Col~ and (pcm.IsTombstoned=1 or ~ pcm.AutoTarget!=pti.AutoTarget or pcm.Predeploy!=pti.Predeploy)~ set @CNT =@CNT +@@rowcount~insert PolicyCollMap(PADBID, TargetCollectionID, AutoTarget, Predeploy,~ IsPolicyUpdated, IsTargetingChanged, IsTombstoned)~ select @padbid, pti.Col, pti.AutoTarget, pti.Predeploy, 0, 1, 0~ from @pti pti~ where not exists(select 1 from PolicyCollMap where PADBID=@padbid and TargetCollectionID=pti.Col)~ set @CNT =@CNT +@@rowcount~update pcm set IsTombstoned=1~ from PolicyCollMap pcm left join @pti pti on pti.Col=pcm.TargetCollectionID~ where pcm.PADBID=@padbid and pcm.TargetCollectionID not in (select Col from @pti )~ set @CNT =@CNT +@@rowcount~if @CNT >0 select @CNT

*** [23000][547][Microsoft][SQL Server Native Client 11.0][SQL Server]The INSERT statement conflicted with the FOREIGN KEY constraint "PolicyCollMap_Collections_FK". The conflict occurred in database "CM_TST", table "dbo.Collections_G", column 'CollectionID'.

*** declare @pti table (Col int primary key, AutoTarget bit, Predeploy bit)~; with PTI(Col, AutoTarget, Predeploy) as (select 0, 1, 0)~insert @pti select Col, max(AutoTarget), max(Predeploy) from PTI group by Col~declare @padbid int; set @padbid=16810068~declare @CNT int; set @CNT =0~update pcm set AutoTarget=pti.AutoTarget,~ Predeploy=pti.Predeploy, IsTargetingChanged=1, IsTombstoned=0~ from @pti pti join PolicyCollMap pcm on pcm.PADBID=@padbid and pcm.TargetCollectionID=pti.Col~ and (pcm.IsTombstoned=1 or ~ pcm.AutoTarget!=pti.AutoTarget or pcm.Predeploy!=pti.Predeploy)~ set @CNT =@CNT +@@rowcount~insert PolicyCollMap(PADBID, TargetCollectionID, AutoTarget, Predeploy,~ IsPolicyUpdated, IsTargetingChanged, IsTombstoned)~ select @padbid, pti.Col, pti.AutoTarget, pti.Predeploy, 0, 1, 0~ from @pti pti~ where not exists(select 1 from PolicyCollMap where PADBID=@padbid and TargetCollectionID=pti.Col)~ set @CNT =@CNT +@@rowcount~update pcm set IsTombstoned=1~ from PolicyCollMap pcm left join @pti pti on pti.Col=pcm.TargetCollectionID~ where pcm.PADBID=@padbid and pcm.TargetCollectionID not in (select Col from @pti )~ set @CNT =@CNT +@@rowcount~if @CNT >0 select @CNT

*** [HY007][0][Microsoft][SQL Server Native Client 11.0]Associated statement is not prepared

CPolicySrc::SetPolicyTargetingInfoEx: Failed to set policy targets for CLIPIL00
UpdateClientPilotingPolicy: failed to target policy 'CLIPIL00' to 'TST003E1', status = -1

Answer 1

As suggested above you should open a support case for this.

Answer 2

@andyk
Thank you for posting in Microsoft Q&A forum.
Have you promote Pre-production Client to production?
We can go to Administration / Overview / Updates and Servicing, select the update and click Promote Pre-production Client.

---

If the response 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 3

Hey

I have the exact same issue: (Policy Manager)

Microsoft SQL Server reported SQL message 547, severity 16: [23000][547][Microsoft][SQL Server Native Client 11.0][SQL Server]The INSERT statement conflicted with the FOREIGN KEY constraint "PolicyCollMap_Collections_FK". The conflict occurred in database "CM_XXX", table "dbo.Collections_G", column 'CollectionID'

Answer 4

I had the same issue and opened a support case with Microsoft.

The engineer removed the collectionID from the TargetcollectionID column in the ClientPilotingConfigs table.