We are getting some SQL Exceptions in arcotriskfort.log and because of this Transactions are not logged in table ARRFSYSAUDITLOG.
Tue Jul 10 06:23:09.012 2018 INFO: pid 2436 tid 5380: 8: 3:608: CallerId[], OrgName[ORGNAME], RelatedInstId[1], RelatedTxnId[865] Tue Jul 10 06:23:09.012 2018 INFO: pid 2436 tid 5380: 2: 3:608: UDS Log : Successfully retrieved the user [<User-Name>] for organization [ORGNAME] Tue Jul 10 06:23:09.075 2018 WARNING: pid 2436 tid 5380: 8: 3:608: SQLException in ARRFDbOperations::insertSysAuditLog : [[DB: Sql Error]] Tue Jul 10 06:23:10.091 2018 WARNING: pid 2436 tid 5380: 8: 3:608: SQLException in ARRFDbOperations::insertSysAuditLog : [[DB: Sql Error]]
Environment
AA 9.x MS SQL Server
Cause
In Previous releases before 9.0, we used to run the SQL scripts manually and it was working fine for all. In 9.0 release, we came up with simplified installer which executes the SQL scripts, bootstraps and made the server UP and running. We eliminated the manual intervention in this release. But here in this, we found that one of the SQL scripts ( create sequence ) failed to execute from Installer which resulted in this error. However we missed capturing this error in the installer log too as we execute the complete SQL script file at once.
Resolution
Follow the steps outlined to resolve the issue --
1. Please Stop the CA AA services ( Risk + Case Management + Strong Auth) 2. Stop the application Application server 3. Please make sure you have backup of DB and then run the SQL
DROP PROCEDURE GETNEXTSEQID; GO
CREATE PROCEDURE GETNEXTSEQID @pServer_ID ARADMIN_TXID_SERVER_ID_TYPE, @pInstance_ID ARADMIN_TXID_INSTANCE_ID_TYPE, @pIP_Address ARADMIN_TXID_IP_ADDRESS_TYPE, @pBlock_Size INT, @pReturn_Seq_ID ARADMIN_TXID_USED_SEQ_NUM_TYPE OUTPUT AS DECLARE @lnewseq INT; DECLARE @errorvar INT SET @errorvar = -1 ;
SET NOCOUNT ON; SELECT @errorvar = USEDSEQNUM FROM ARADMINTXID WHERE SERVERID = @pServer_ID AND INSTANCEID = @pInstance_ID;
IF @errorvar = -1 BEGIN SET @pReturn_Seq_ID = 0 INSERT INTO ARADMINTXID (SERVERID, INSTANCEID, IPADDRESS, USEDSEQNUM) VALUES (@pServer_ID, @pInstance_ID, @pIP_Address, @pBlock_Size);
RETURN END ELSE BEGIN SET @pReturn_Seq_ID = @errorvar; SET @lnewseq = @pReturn_Seq_ID + @pBlock_Size;
UPDATE ARADMINTXID SET USEDSEQNUM = @lnewseq, IPADDRESS = @pIP_Address WHERE SERVERID = @pServer_ID AND INSTANCEID = @pInstance_ID; END RETURN
4. Recompile the Procedure as SQL server may have it cached, a Restart of SQL server can also be done if possible. 5. Start the CA AA services ( Risk + Case Management + Strong Auth) 6. Start the Application server