1

I entered the following expression into the expression builder for a Lookup activity based on a query:

@replace('SELECT TOP 1 * FROM dbo.Tag WHERE (ConversationId = {Id} AND Key=''assignment'')','{Id}',string(pipeline().parameters.ConversationId))

It causes the activity to fail with the following error :

Incorrect syntax near the keyword 'Key'

...I've been trying to find the right syntax for the last 45 minutes. Help!

If the problem is indeed that I'm incorrectly escaping a single quote then I'm extremely confused because every StackOverflow answer says that this is the right way to escape them (two single quotes in a row).

Note: Key is the actual column name. And it does ave a value that is the literal string 'assignment'.

jeancallisti
  • 1,046
  • 1
  • 11
  • 21
  • 1
    I ran the code and setting a variable to that @replace function worked fine. `{ "name": "myvar", "value": "SELECT TOP 1 * FROM dbo.Tag WHERE (ConversationId = 5 AND Key='assignment')" }` – Zorkolot May 02 '23 at 13:55
  • I don't understand your comment. I don't understand why it's wrapped in JSON. Is that the result of converting the expression used in a "set variable" activity to whatever underlying language ADF uses? How did you achieve that? see https://stackoverflow.com/questions/76055936/an-easy-way-of-running-code-snippets-written-in-azure-data-flow-script-express – jeancallisti May 02 '23 at 13:57
  • 1
    When you run a debug run, you can look at the input and output results for a ADF variable during the run, the output is presented as JSON, in this case I used a Set Variable step. – Zorkolot May 02 '23 at 13:59

1 Answers1

2

"Key" is a reserved keyword in SQL. the query shouldn't be :

WHERE Key = 'something'

it should be

WHERE dbo.[Key] = 'something'

this was purely a SQL problem, not an ADF problem.

jeancallisti
  • 1,046
  • 1
  • 11
  • 21