WPs will not start, SET NOCOUNT OFF error message

Products

CA Automic Workload Automation - Automation Engine CA Automic One Automation

Issue/Introduction

When attempting to restart the WPs and CPs, especially with a COLD start (done automatically after a manual load of data to update the database), the WP ends abnormally with messages like these:

20250208/120718.183 - U00029108 UCUDB: SQL_ERROR Database handles DB-HENV: 6a5c7230 DB-HDBC: 6a5c7310
20250208/120718.183 - U00003591 UCUDB - DB error info: OPC: 'SQLExecDirect' Return code: 'ERROR'
20250208/120718.183 - U00003592 UCUDB - Status: '42000' Native error: '523' Msg: 'A trigger returned a resultset and/or was running with SET NOCOUNT OFF while another outstanding result set was active.'
20250208/120718.183 - U00003594 UCUDB Ret: '3590' opcode: 'UPDT' SQL Stmnt: 'UPDATE EH SET EH_Status = ?,EH_ChildFlags = ? WHERE EH_AH_Idnr = ?'
20250208/120718.183 - U00003620 Routine 'JPEXEC_R' forces trace because of error.
20250208/120718.183 - U00003590 UCUDB - DB error: 'SQLExecDirect', 'ERROR ', '42000', 'A trigger returned a resultset and/or was running with SET NOCOUNT OFF while another outstanding result set was active.'
20250208/120718.240 - U00003449 Output to the TRACE file is finished.
20250208/120718.240 - U00003100 Memory TRACE was opened with the switches '0000000000000000'.
20250208/120718.240 - U00003380 Server 'AUTOMIC#WP001' version '21.0.12+build.1727776881277' (Runtime '0/00:00:08', Log# '1', Trc# '0').
20250208/120718.240 - U00003491 There is a time difference of '0/00:00:00' or '0' seconds to the Primary Server.
20250208/120718.240 - U00003375 Server usage of the last minute '0%', the last 10 minutes '0%' and the last hour '0%'.
20250208/120718.240 - Server Typ C Host Port StartTime LastUpdateTime
20250208/120718.240 - -------------- --- - ----------------------- ----- ------------------- -------------------
20250208/120718.240 - AUTOMIC#WP001 PWP * server1.example.com 2270 2025-02-08 12:07:10 2025-02-08 12:07:15
20250208/120718.240 - U00003590 UCUDB - DB error: 'SQLExecDirect', 'ERROR ', '42000', 'A trigger returned a resultset and/or was running with SET NOCOUNT OFF while another outstanding result set was active.'
20250208/120718.240 - U00011816 Server 'AUTOMIC#WP001': Termination initiated.

Environment

Database: SQL Server
Automation Engine: Any version

Cause

Incorrect settings on the database.

Resolution

This error usually occurs when the AAI (Automation Analytics & Intelligence) connector was set up or used at some point on the Automic system.

If AAI is still used, confirm with a DBA that 'disallow results from triggers' is set on the database.

If AAI is not used or as a workaround if the DBA confirms 'disallow results from triggers' is on (the column value_in _use must be set to 1):

The EXECUTION_TRIGGER setting needs to be changed to N in the UC_SYSTEM_SETTINGS variable in Client 0. Ideally this should be done by logging in to the AWI as an administrator and updating the variable directly. If this is not possible, the following update statement can be used (use this with extreme caution): UPDATE OVW SET OVW_Value1 = 'N' WHERE OVW_VValue = 'EXECUTION_TRIGGER' AND OVW_Value1 = 'Y' AND OVW_OH_IDNR in (select oh_idnr from oh where oh_client = 0 and oh_name = 'UC_SYSTEM_SETTINGS')
Attempt to start the WPs
The WPs might not start still at which point a DBA needs log on to the database through something like SQL Server Management Studio and expand the database, expand the AH table, expand Triggers, right-click on TR_AH_TIMESTAMP2_4 and choose disable.
Follow the same steps to disable the TR_EH_STATUS_BLOCKED trigger in the EH table
There is another trigger that may be on the database itself, TR_XEVENTS - expand the database, expand Programmability, expand Database Triggers, and disable this trigger there
Attempt to start the WPs