Multi-line SQL in variable does not work in SQL JOBS

Document ID : KB000084565
Last Modified Date : 08/06/2018
Show Technical Document Details
Issue:
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 introduced 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 
Environment:
OS: All
Cause:
Cause type:
By design
Root Cause: N/A
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
 
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.