Question

Workflow Custom Query Update will not accept table name

Workflow Version 10

Updated March 11, 2020

asked on March 9, 2020 • Show version history

Can anyone point out to me why one Custom Query (a Delete) will accept the db name and another Custom Query (an Update) will not? I even cut and pasted the Delete one into the Update one (all within the same workflow) and the update one never works.

Not working:

0 0

Answer

SELECTED ANSWER

replied on March 10, 2020

Hi Connie,

Is there a reason you can't use the "Update Data" activity to run this query, rather than using a custom query?

Thanks, Dan

1 0

replied on March 11, 2020 • Show version history

Daniel, this combination works perfectly for another database, but I am just learning how to use these at the moment and I just copied the first wf and altered it for this second form where the data goes to a different lookup table. When building the first one, we tried a number of combinations and this combination was the one that worked.

The main answer to your question (if I remember correctly) is because I'm trying to do a number of tasks all in one workflow, but honestly, I can't really remember at this point. But thanks for the idea, I can try building from scratch a new workflow with just that one piece and using an update data activity instead.

0 0

Replies

replied on March 11, 2020 • Show version history

WORKING!!! Thanks, everyone!

Due to Daniel's comments, I disabled the Custom Query (which configuration works in another workflow for another lookup table, don't know why) and I rebuilt the task using an Update activity. It Worked!

I was also very interested to learn that the db address isn't even needed at the top row, so thanks for that Samuel!

Thanks for your patience with me, everyone. This is all new to me, this SQL building etc. I have had absolutely no training for it and I so much appreciate this source of help.

A huge expression of Thanks! must also go out to Matthew Tingey, who contacted me by phone and email to help me build the first of these workflows. He gave me a crash course in accessing lookup tables and helped me troubleshoot until I got it doing everything I wanted it to.

2 0

replied on March 9, 2020

Not sure exactly. However, you shouldn't need the database name in your query as it is part of the Data Source external object already. The query you're running is already running against a specific database. See below.

One possibility is that the SQL ODBC driver you're using for the connection doesn't like the "$" in the table name. Try changing the Data Source to use the SQL Native Client (direct) instead.

1 0

replied on March 11, 2020

Samuel, interesting point. Yes, I do have the connection to the db set up the way you are showing and I know it works because the connection in the delete query works and that is to the same db table.

But if that is the case, then why is the query even bothering to stop me here at this point? Regardless! I just tried it and it skipped right past that point!

Unfortunately, it has now stopped at the SET line and I'm getting this message:

Now compare to the working one, the first workflow that works with a different form and a different lookup table:

0 0

replied on March 10, 2020

Does your table name actually contain single quotes?

1 0

replied on March 11, 2020

Miruna, no and I have tried the workflow with/without the quotes, with/without the underscore between the words, with/without the brackets. The weird thing is that it works in one custom query and not in another, all within the same workflow.

0 0

replied on March 11, 2020

Your screenshot above shows that when you tried the name without quotes you also left out the $ at the end.

If you look in SQL Server Management Studio, what is the name listed in the Tables node?

Are you using the same data sources in all these activities?

0 0

replied on March 11, 2020

Yes, I tried that as well. I tried every combination I could think of. When I built the table, I did not use the $ in the name. I don't know why the SQL software added that, so I tried it with and without.

0 0

You are not allowed to follow up in this post.

Question

Question

Workflow Custom Query Update will not accept table name

Answer

Replies

Sign in to reply to this post.