Multi-line SQL stored in variable does not work in SQL JOBS
Article ID: 84565
Updated On:
Products
CA Automic Workload Automation - Automation Engine
Issue/Introduction
Error Message :
Incorrect syntax near ','.
U02004025 Job-script execution was aborted.
In SQL JOBS, SQL queries containing multiple lines and thus the carriage returns work.
However, when a multi-line query is fed to the SQL JOBS via a variable through a VARA EXEC or SCRI object the query fails.
This is the situation in which the SQL JOB fails:
Incorrect syntax near ','.
U02004025 Job-script execution was aborted.
In SQL JOBS, SQL queries containing multiple lines and thus the carriage returns work.
However, when a multi-line query is fed to the SQL JOBS via a variable through a VARA EXEC or SCRI object the query fails.
This is the situation in which the SQL JOB fails:
- The process tab of the SQL JOBS only contains a variable (&SQL_QUERY#)
- A VARA EXEC or SCRI defines the variable &SQL_QUERY# and calls the SQL JOBS (as an executable object or with ACTIVATE_UC_OBJECT respectively)
- The variable contains a multi-line the SQL query
Cause
By design
Environment
OS: All
Resolution
Workaround: In order to make this mechanism work, remove carriage returns from the SQL query when the query is part of a variable.
Fix Status: No Fix
Fix Status: No Fix
Additional Information
A remark has been added to the documentation here: Automic Scripting Guide > Ordered by Function > Activate Objects > :READ
With a :READ statement, you can read values that have been put to the input buffer. In this case, do not use text values with line breaks in the input buffer. :READ ignores the characters after the first line break. For more details, see PUT_READ_BUFFER.
It is important to realize that the command :READ is used behind-the-scenes in several areas of the Automation Engine to pass parameters (e.g., passing EXEC VARA parameters). When parameters are passed to the executable object via the :READ buffer, multi-line content can never be used. See the documentation of the :READ script statement mentioned above for more information.
With a :READ statement, you can read values that have been put to the input buffer. In this case, do not use text values with line breaks in the input buffer. :READ ignores the characters after the first line break. For more details, see PUT_READ_BUFFER.
It is important to realize that the command :READ is used behind-the-scenes in several areas of the Automation Engine to pass parameters (e.g., passing EXEC VARA parameters). When parameters are passed to the executable object via the :READ buffer, multi-line content can never be used. See the documentation of the :READ script statement mentioned above for more information.
Feedback
Yes
No
Powered by
