SQL JOBS fails on v24 with Incorrect syntax errors

Products

CA Automic Workload Automation - Automation Engine

Issue/Introduction

When the terminator character is set to '@', any references to '@' terminates the script. This was not the case in v21.

SQL_SET_STATEMENT_TERMINATOR TERM=@;
DECLARE @ErrorMessage NVARCHAR(4000);

Job Report:

Incorrect syntax near ''.
U02004025 Job-script execution was aborted.
U02012006 SQL Rollback executed

Environment

AE v24.4.x and SQL Agent 24.4.+ or 21.0.14+

SQL Agent 21.0.13 <- Issue does not occur
SQL Agent 24.3 <- Issue does not occur
SQL Agent 21.0.14 <- Issue occurs
SQL Agent 24.4.0 <- Issue occurs
SQL Agent 24.4.1 <- Issue occurs

Cause

Example for Oracle:

SQL_SET_STATEMENT_TERMINATOR TERM=@;
BEGIN
  dbms_output.put_line('[email protected]');
END;@

Before the change, this was executed correct and the answer was:

2025-08-12 13:27:00 - U02000005 Job 'JOBS.SQL' with RunID '6718012' is to be started.
2025-08-12 13:27:00 - U02012001 Successfully established connection to '<oracle-server>:1521/automic' with user 'automic'. 
2025-08-12 13:27:00 Oracle 
2025-08-12 13:27:00 Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production 
2025-08-12 13:27:00 Version 19.19.0.0.0 
2025-08-12 13:27:00 - U02012014 JDBC Driver version: 23.6.0.24.10 
2025-08-12 13:27:00 BEGIN 2025-08-12 13:27:00 dbms_output.put_line('[email protected]'); 
2025-08-12 13:27:00 END; 2025-08-12 13:27:00 - U02004026 Job script ended normally.

with REPORT

[email protected]

After the change:

2025-08-12 13:38:22 - U02000005 Job 'JOBS.SQL' with RunID '6717007' is to be started.
2025-08-12 13:38:22 - U02012001 Successfully established connection to '<oracle-server>:1521/automic' with user 'automic'. 
2025-08-12 13:38:22 Oracle 2025-08-12 13:38:22 Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production 
2025-08-12 13:38:22 Version 19.19.0.0.0 
2025-08-12 13:38:22 - U02012014 JDBC Driver version: 23.6.0.24.10 
2025-08-12 13:38:22 BEGIN 
2025-08-12 13:38:22 dbms_output.put_line('user 
2025-08-12 13:38:22 ORA-06550: line 2, column 25: PLS-00103: Encountered the symbol "user" when expecting one of the following: ( ) - + case mod new not null <an identifier> <a double-quoted delimited-identifier> <a bind variable> table continue avg count current exists max min prior sql stddev sum variance execute multiset the both leading trailing forall merge year month day hour minute second timezone_hour timezone_minute timezone_region timezone_abbr time timestamp interval date <a string literal with character set specificati https://docs.oracle.com/error-help/db/ora-06550/ 
2025-08-12 13:38:22 - U02004025 Job-script execution was aborted. 
2025-08-12 13:38:22 - U02012006 SQL Rollback executed

Resolution

Workaround:

Downgrade the Agent SQL to 21.0.13 or 24.3.0

Solution:

Update to a fix version listed below or a newer version if available.

Fix version:
Component(s): Agent SQL
Automation.Engine 21.0.15 - Planned release October 2025
Automation.Engine 24.4.2 - Planned release October 2025

Additional Information

Defect ID: DE174238

Public Title: SQL Agent Job runs successfully in v21 but fails in v24

Public Description: A problem has been fixed where changing the 'SQL_SET_STATEMENT_TERMINATOR' could lead to incorrect results and cause an invalid SQL.