In the "Execute SQL" action, you can insert reference field. But can you insert a reference field which contains the entire query? My testing shows this is not possible?
I have a list which has a multiple line type field, this field contains the entire SQL query. In the workflow, I query this list and store the query in a multiple line variable (also tried single line variable but no difference). Then insert this variable into "Execute SQL" action. But it always runs into error like "Error performing database operation ...".
However, if I place the same query directly into the query box, workflow runs fine without any issues.
I have also tested the return value of the variable, it seems that it just returns the exact query statement, so I do not understand why it does not work. Does it mean the return value of the variable is parsed differently into "Execute SQL" action?
The purpose of doing so is to maintain the query easily so users do not have to go into workflow and adjust the action.
Any help will be appreciated.
the actions help dialog says something about the "Escape reference values for use in strings":
"Use this option to ensure that any value chosen from the Insert Reference button is escaped for use in a SQL string. If the entire SQL statement is contained in a single variable, then this option must be unchecked. "
Did you try to uncheck this checkbox in your actions configuration?
I am sorry that I just saw your reply. I thought no one would ever reply to my question.
I will give your suggestion a try and get back to you later.