000033487 - RSA Archer Users deleted with SQL causes error opening Questions due to History Log

Document created by RSA Customer Support Employee on Jul 11, 2016Last modified by RSA Customer Support Employee on Apr 21, 2017
Version 2Show Document
  • View in full screen mode

Article Content

Article Number000033487
Applies ToRSA Product Set: Archer
RSA Version/Condition: 5.5, 6.1.1
IssueReceiving an error opening questions and the stacktrace indicates an issue to the History Log:
<ExceptionType>System.AggregateException, mscorlib, Version=, Culture=neutral, PublicKeyToken=b77a5c561934e089</ExceptionType>
<Message>One or more errors occurred.</Message>
  at System.Threading.Tasks.Task.WaitAll(Task[] tasks, Int32 millisecondsTimeout, CancellationToken cancellationToken)
  at ArcherTech.Web.Modules.GenericContent.GenericContentDataManager.GetRecordData(LevelRecordDataRequest levelRecordDataRequest)
  at ArcherTech.Web.Modules.GenericContent.Views.RecordPresenter.LoadRecordData()
  at ArcherTech.Web.Modules.GenericContent.Views.RecordPresenter.OnViewLoaded()
  at ArcherTech.Web.ArcherTechPage`2.OnLoad(EventArgs e)
  at Security2000.GenericContent.Record.OnLoad(EventArgs e)
  at System.Web.UI.Control.LoadRecursive()
  at System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint)
  <ExceptionType>System.ArgumentNullException, mscorlib, Version=, Culture=neutral, PublicKeyToken=b77a5c561934e089</ExceptionType>
   Value cannot be null.
   Parameter name: source
   at System.Linq.Enumerable.SingleOrDefault[TSource](IEnumerable`1 source, Func`2 predicate)
   at ArcherTech.Web.Modules.GenericContent.DataManagers.ContentLoader.GetHistoryLogContents(SessionContext session, IResourceService resourceService, LevelRecordDataContainer levelRecordDataContainer, Nullable`1 contentId)
   at ArcherTech.Provider.Threading.ThreadingFactory.<>c_DisplayClass2.<WrapAction>b_1()
            at System.Threading.Tasks.Task.Execute()</StackTrace>
CauseThis is caused because users in the History Log field were removed by a SQL script rather than through the user interface. If the user was deleted through the user interface, the History Log would not be causing these errors.
Using SQL queries is not a recommended approach for several reasons:

1.    The database schema is different between Archer versions.  For example, translation tables introduced.
2.    SQL bypasses Archer code logic, permissions, and auditing.
3.    Custom SQL queries are not supported.
4.    SQL commands should not be used unless it's absolutely necessary to resolve an issue OR functionality does not exist.
5.    The data is spread across many, many tables.

The recommended and supported method is to use the Archer Web Services and REST API.
ResolutionThere are two options to resolve this issue. The first would be to re-add the users that were deleted from the database, OR use the following script for resolution:
            FROM    tblUser
            WHERE   [user_id] = @userId )
        UPDATE TOP ( 10000 ) ch
        SET     ch.action_login = @userId
        FROM    tblIVContentHistory ch
        LEFT OUTER JOIN tblUser u ON u.user_id = ch.action_login
        WHERE   u.user_id IS NULL;
        PRINT 'OPERATION FAILED - please provide a valid user id';

This script will update all previously held History Log information by the deleted users to point at a new user. The user id can be found by mousing over the user in the user interface. This can either be a 'dummy' newly created user or a user already in the system.
If the History Log is system or locked, it is possible you will have to create a new History Log field and use the Link option rather than the default Grid type to ultimately resolve this issue. As with any SQL script, a backup should be performed before testing.