question

andyk-1490 avatar image
0 Votes"
andyk-1490 asked Garth commented

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

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


mem-cm-general
· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Open a support case here is probably the best path as this will almost certainly require modifying the database directly which shouldn't be done generally without assistance from support. I also suggest filing a feedback item in the console so that the dev team can review and possibly put in a mechanism to prevent others from also doing this or giving folks a way to fix this themselves in the console.

1 Vote 1 ·

Hi @Jason-MSFT

My company does not have eligible support plan for me to open a support case, but i will certainly submit a feedback item.

Many thanks

0 Votes 0 ·
GarthJones-MVP avatar image
0 Votes"
GarthJones-MVP answered

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

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

AllenLiu-MSFT avatar image
0 Votes"
AllenLiu-MSFT answered andyk-1490 commented

@andyk-1490
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.


· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Yes, Pre-Production had already been promoted before i deleted the collection

0 Votes 0 ·
mmndk avatar image
0 Votes"
mmndk answered

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'

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

MattL-5267 avatar image
0 Votes"
MattL-5267 answered Garth commented

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.

· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Excellent - that could save £500

I can test in lab first.

Appreciate you taking the time to share.

0 Votes 0 ·

This is completely unsupported and might cause more problems than it fixed. Just be prepared for it.

1 Vote 1 ·