IBM Support

'COMException (0x80004005): Subquery returned more than 1 value. This is not permitted when the subquery follows...' error caused by invalid data inside the database table "sactrlinfo"

Troubleshooting


Problem

User performs a task (for example clicks "Maintain - Rights - Users"). User makes a change.

User presses 'Save'. An error appears.

Symptom

The error will vary depending on the environment/version. Some examples are below:

Example #1:

Standard Error
Number: 5
Source: ControllerProxyClient.ISetCacheT_InsertRecords......frm.SecurityGroups.PerformeS ave
Description: System.Web.Services.Protocols.SoapException: Server was unable to process request.--->
System.Runtime.InteropServices.COMException (0x80004005): Subquery returned more than 1 value. This is not permitted
when the subquery follows 0, !=, <, <=, >, >= or when the subquery is used as an expression....

Example #2:

Details
Number: 5
Source: ControllerProxyClient:FrangoClient.clsRightsCache.InsertRecords: FrangoClient.clsUsers.Insert:FrangoClient.frmUserAndInstallationRights.
SaveUser:FrangoClient.frmUserAndInstallationRights.IFile_DoSave
Description: System.Web.Services.Protocols.SoapException: Server was
unable to process request. ---> System.Runtime.InteropServices.
COMException (0x80004005): Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
   at FrMiscST.MedlemSetTClass.InsertRecords(String sGuid, Recordset
rsRecord, String sUser, Int32 lLLocale)
   at Cognos.Controller.Proxy.CCRWS.MedlemSetT_InsertRecords(String
sGuid, DataSet rsRecord, String sUser, Int32 lLLocale)
   --- End of inner exception stack trace ---
   at Microsoft.VisualBasic.ErrObject.Raise(Int32 Number, Object
Source, Object Description, Object HelpFile, Object HelpContext)
   at Cognos.Controller.Common.RaiseErrCli.RaiseError(Int32 lErrNo,
String sErrSource, String sErrDesc, String sErrHelpFile, Int32
lErrHelpContext)
   at Cognos.Controller.Forms.Form.frmUserAndInstallationRights.
IFile_DoSave()
   at Cognos.Controller.Forms.Form.frmUserAndInstallationRights.
IForm_DoClick(Int32 lCmd, String& sTag)

Example #3 (Controller 10.3.1):

image-20181002074343-1

Cause

TThere are several known causes for similar errors.
  •  TIP: See separate IBM Technote #0960392 for more examples.
 
This Technote specifically relates to the scenario where the cause is that the database table "sactrlinfo" contains some invalid entries.
More Information:
If the customer enables the optional Controller functionality 'system audit log', then Controller will keep track of changes in the system. As part of this mechanism, it will temporarily store some records inside the database table:  sactrlinfo
  • Records in this table are temporary (they are typically deleted shortly after they are created
Specifically, if a user performs some types of actions (in the Controller client) then an entry is added into this table.
  • The entry should only be in there for a second (or so).
  • Then the user's operation has completed, the row (inside 'sactrlinfo') should be deleted.
  • Therefore, the normal state for this table (for most of the time) is that it would be empty.
Occasionally it is possible that the table will contain values/entries long-term. Two potential causes are:
(1) This 'corrupt'/'invalid' data has been left here because of a previous program crash.
  • In other words, if (at some point in the past) the system hung/crashed (whilst in the middle of a user making a change to the system) then it is possible that data could unexpectedly remain in this table long-term.
(2) A "race condition" (caused by two database triggers firing at the same time, both of which involved the sactrlinfo table) caused a deletion to not occur.

Environment

Problem only occurs when the system audit log is activated

  • When the System Audit Log is turned off the functionality works OK (it is possible to make changes again).

Resolving The Problem

Fix:
Delete the corrupt/invalid rows inside the table "sactrlinfo".
  • NOTE: It is safe to delete the contents of sactrlinfo, because Controller expects this table to be empty most of the time.
However, we recommend the customer use the database optimize action: Deleting database entries should come after, only if the optimize action had no effect, with the purpose to unblock potentially essential business processes.
Steps:
There are two methods:
Method #1 - Recommended (easiest and safest)
Run a database optimize (from within the Controller client GUI).
IBM R&D recommend this method because:
  • It ensures the database is not in use (single user mode)
  • It does not require special access to the database (therefore the end user can perform the task, without assistance from I.T. department).
1. Ensure no user logged onto Controller
2. Click "Maintain - User - Single User"
3. Click "Maintain - Database - Optimize"
4. Choose/select all available options *except* there is no need to tick 'rebuild indexes' (which can slow down the database optimise a lot)
5. Click "Run".
Method #2
Delete the "bad" rows manually.
  • TIP: In some (rare) circumstances, using this method is necessary if Method #1 fails to solve the problem.
1. As a precaution:
  • Ensure no users are in the system (downtime)
  • Create a full backup of your SQL database
2. Launch your database tool (for example "SQL Management Studio")
3. Locaste the Controller database, and expand 'Tables'
4. Right-click on table sactrlinfo
5. Choose 'Edit top 200 rows'
6. Delete the entries (there will typically be between 1 and 10 in there)
7. Test.

[{"Business Unit":{"code":"BU059","label":"IBM Software w\/o TPS"},"Product":{"code":"SS9S6B","label":"IBM Cognos Controller"},"Component":"","Platform":[{"code":"PF033","label":"Windows"}],"Version":"All Versions","Edition":"","Line of Business":{"code":"LOB10","label":"Data and AI"}}]

Historical Number

1037166

Document Information

Modified date:
25 July 2019

UID

swg21364766

Page Feedback