Publish to ITShop fails after a sync with an "alternate key exists" error (142131)

Publish to ITShop fails after a sync with an "alternate key exists" error

The FullSync (VI_ADS_Domain_Fullsync) fails at the last step of "Publish ADSGroups to ITShop" due to a particular group:

ErrorMessages = [810222] Error executing script 'VI_AssignADSGroupsToITShop'.
[810008] Could not save object Service items (GROUPNAME).
[810237] There is already an object using this alternate key.
at StdioProcessor.StdioProcessor._Execute(Job job)
at VI.JobService.JobComponents.ScriptComponent.Activate(String task)
at VI.JobService.JobComponents.ScriptComponent._TaskScriptExec()
at VI.DB.Scripting.ScriptInstance.Eval(String key, Object[] parameters)
---- Start of Inner Exception ----
at DynScripts.VIScripts_DFCFA5B0B7524150B34138228C5F081C.VI_AssignADSGroupsToITShop(String Domain)
at VI.DB.Implementation.SingleDbObject.Save()
---- Start of Inner Exception ----
at VI.DB.Implementation.SingleDbObject.Save()
at VI.DB.Implementation.SingleDbObject._InsertIntoDB(Boolean checkRights)
at VI.DB.Implementation.SingleDbObject._CheckAlternateKey()''

We have removed the entry for the particular group in 'ADSGroup', but the error persists.  Which other key is conflicting which is preventing the FullSync to be completed?

A duplicate entry exists in 'AccProduct', for example.

The reasons behind the error can be extracted from the error message itself:

ErrorMessages = [810222] Error executing script 'VI_AssignADSGroupsToITShop'.
[810008] Could not save object Service items (GROUPNAME).
[810237] There is already an object using this alternate key.

The first line gives us the top level object for this exception, which in this case is the script being executed in the last step of the FullSync process.  At this point in time the FullSync step (ADSComponent - FullSync) has already completed and it has transitioned to the next step in the process.

The second line tell us what it tried to do, which in this case was saving an object of type "Service items".  You can find out which table this refers to by running this SQL snippet:

select * from DialogTable where DisplayName = 'Service items'

The third line tells us was why it failed, which in this case was an Alternate Primary Key (APK) violation.  The following may be used to confirm which columns are part of the APK definition for the previously determined object type:

select ColumnName, Commentary, LimitedValues from DialogColumn where TableName = (
   select TableName from DialogTable where DisplayName = 'Service items'
)
and IsAlternatePKMember = 1
order by 1

Having this information you would still have to look at the script to investigate why this occurs.  The mostly likely reason is that this group has previously been added to the database, was deleted (manually) and has then been added again by the FullSync.

When the ADGroup was manually deleted the AccProduct was probably left over.  The newly inserted product obviously does not have a reference to an AccProduct yet.  Therefore the script tries to create a new AccProduct.  The properties of the AccProduct derive from the ADSGroup, which is why there is an APK violation for group that has identical values.

Essentially you might need to delete the AccProduct, which you should only do through either Manager or ObjectBrowser (not SQL).  It would then be created again after the next FullSync.

Please check if the AccProduct with that name still has a reference to an existing ADGroup entry.  If that is not the case you can use these steps to clean up:

In Manager select the "Database" menu and choose "Check data consistency..."

- Press CTRL+A
- Right-click and choose "Disable"
- Select AccProduct in the list, Right-click and choose "Enable"
- Click the "Gear and wrench" icon to access the "Test options" dialog:



Select all options in the "Test options" dialog and click "Ok":



- Click the "Play" icon to run the consistency check:



You can use the repair option to fix most issues.