000034897 - When creating nested Groups in RSA Archer 6.1.x, an exception occurs: Violation of PRIMARY KEY constraint 'PK_tblExpandedGroupHierarchy'

Document created by RSA Customer Support Employee on Mar 4, 2017Last modified by RSA Customer Support Employee on Apr 22, 2017
Version 2Show Document
  • View in full screen mode

Article Content

Article Number000034897
Applies ToRSA Product Set: Archer
RSA Version/Condition: 6.1.x
IssueUnable to create nested Groups in certain conditions.
The following exception is logged in the w3wp log file:

<E2ETraceEvent xmlns="http://schemas.microsoft.com/2004/06/E2ETraceEvent">
    <System xmlns="http://schemas.microsoft.com/2004/06/windows/eventlog/system">
        <Source Name="Archer.Silverlight.WCF" />
        <Execution ProcessName="w3wp" ProcessID="15740" ThreadID="34" />
        <AssemblyVersion>6.1.0.1159</AssemblyVersion>
    </System>
    <ApplicationData>
        <TraceData>
            <DataItem>
                <TraceRecord Severity="Error" xmlns="http://schemas.microsoft.com/2004/10/E2ETraceEvent/TraceRecord">
                    <TraceIdentifier>Archer.Silverlight.WCF</TraceIdentifier>
                    <UserId>972</UserId>
                    <LogReferenceId>022717-194006-0657</LogReferenceId>
                    <Description>Violation of PRIMARY KEY constraint 'PK_tblExpandedGroupHierarchy'. Cannot insert duplicate key in object 'dbo.tblExpandedGroupHierarchy'. The duplicate key value is (1675, 1962).
The statement has been terminated.
SQL statement: usp_ui_group_members @userToken = '3B029E177490699D3CD6BE9397D1824E', @groupId = 1675, @memberXML  = '&lt;ROOT&gt;&lt;V a="I" b="2726" /&gt;&lt;/ROOT&gt;', @date = '2/27/2017 7:40:05 PM'</Description>

                    <AppDomain>/LM/W3SVC/1/ROOT/Archer-1-131326654496968204</AppDomain>
                    <Exception>
                        <ExceptionType>ArcherTech.Kernel.Utility.Data.ArcherDbException, ArcherTech.Kernel, Version=6.1.0.1159, Culture=neutral, PublicKeyToken=null</ExceptionType>
                        <Message>Violation of PRIMARY KEY constraint 'PK_tblExpandedGroupHierarchy'. Cannot insert duplicate key in object 'dbo.tblExpandedGroupHierarchy'. The duplicate key value is (1675, 1962).
The statement has been terminated.
SQL statement: usp_ui_group_members @userToken = '3B029E177490699D3CD6BE9397D1824E', @groupId = 1675, @memberXML  = '&lt;ROOT&gt;&lt;V a="I" b="2726" /&gt;&lt;/ROOT&gt;', @date = '2/27/2017 7:40:05 PM'</Message>

                        <Source>ArcherTech.Kernel</Source>
                        <StackTrace>
   at ArcherTech.Kernel.Utility.Data.ArcherSqlDatabase.WrapDatabaseCall(DbCommand command, Action methodToWrap)
   at ArcherTech.Kernel.Utility.Data.ArcherSqlDatabase.ExecuteNonQuery(DbCommand command)
   at ArcherTech.Kernel.DataSource.Db.GroupDataSource.SaveGroupMembers(Int32 groupId, IList`1 groupMembersIds)
   at ArcherTech.Kernel.Brokers.GroupBroker.SaveChangesForGroupMembers(Int32 groupId, IList`1 newGroupMembers, Boolean isUpdate)
   at ArcherTech.Kernel.Brokers.GroupBroker.UpdateGroup(Group group, IList`1 childGroups, IList`1 childUsers, IList`1 parentGroups)
   at ArcherTech.Kernel.Brokers.GroupBroker.Save(GroupContainer groupContainer, GroupSaveOptions saveOptions)
   at ArcherTech.Kernel.Managers.GroupManager.Save(SessionContext sessionContext, GroupContainer groupContainer)
   at SyncInvokeSaveGroup(Object , Object[] , Object[] )
   at System.ServiceModel.Dispatcher.SyncMethodInvoker.Invoke(Object instance, Object[] inputs, Object[]&amp; outputs)
   at ArcherTech.Web.WCF.Behavior.SaveResultFormatter.Invoke(Object instance, Object[] inputs, Object[]&amp; outputs)
   at System.ServiceModel.Dispatcher.DispatchOperationRuntime.InvokeBegin(MessageRpc&amp; rpc)
   at System.ServiceModel.Dispatcher.ImmutableDispatchRuntime.ProcessMessage5(MessageRpc&amp; rpc)
   at System.ServiceModel.Dispatcher.ImmutableDispatchRuntime.ProcessMessage11(MessageRpc&amp; rpc)
   at System.ServiceModel.Dispatcher.MessageRpc.Process(Boolean isOperationContextSet)</StackTrace>
                    </Exception>
                </TraceRecord>
            </DataItem>
        </TraceData>
    </ApplicationData>
</E2ETraceEvent>


 
CauseThe tblExpandedGroupHierarchy table is missing the expanded_group_hierarchy_id column.
Resolution
  1. Perform a SQL Backup of the Instance database in case a restore is required.
  2. Run the following SQL script against the Instance database to add the missing column.
    SET NOCOUNT ON;
    IF EXISTS ( SELECT  1
                FROM    INFORMATION_SCHEMA.COLUMNS
                WHERE   TABLE_NAME = 'tblExpandedGroupHierarchy' )
        AND NOT EXISTS ( SELECT 1
                         FROM   INFORMATION_SCHEMA.COLUMNS
                         WHERE  TABLE_NAME = 'tblExpandedGroupHierarchy'
                                AND COLUMN_NAME = 'expanded_group_hierarchy_id' )
        ALTER TABLE dbo.tblExpandedGroupHierarchy
        ADD expanded_group_hierarchy_id INT IDENTITY(1,1) NOT NULL;

    IF EXISTS ( SELECT  1
                FROM    INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE
                WHERE   CONSTRAINT_NAME = 'PK_tblExpandedGroupHierarchy' )
        ALTER TABLE dbo.tblExpandedGroupHierarchy
        DROP CONSTRAINT PK_tblExpandedGroupHierarchy;
    IF EXISTS ( SELECT  1
                FROM    INFORMATION_SCHEMA.COLUMNS
                WHERE   TABLE_NAME = 'tblExpandedGroupHierarchy'
                        AND COLUMN_NAME = 'expanded_group_hierarchy_id'
                        AND DATA_TYPE = 'int' )
        ALTER TABLE dbo.tblExpandedGroupHierarchy
        ALTER COLUMN expanded_group_hierarchy_id BIGINT NOT NULL;
    IF NOT EXISTS ( SELECT  1
                    FROM    INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE
                    WHERE   CONSTRAINT_NAME = 'PK_tblExpandedGroupHierarchy' )
        ALTER TABLE dbo.tblExpandedGroupHierarchy
        ADD CONSTRAINT PK_tblExpandedGroupHierarchy
        PRIMARY KEY (expanded_group_hierarchy_id);
    GO

Attachments

    Outcomes