Question

invalid object name in "Query Data" - why?

Workflow

Updated July 13, 2022

asked on July 12, 2022

I am connected to a database and am using "Query Data". I am searching on "Last Name" for Tudor, which obviously shows up in SQL Server Management Studio, like this:

I am doing a test query in my workflow like this:

But I always get this:

What am I doing wrong?

If it matters, I am connected to the database through a connection string, not ODBC or direct connection.

And, the next step would be to pull the other data referenced here into a token, like Employee Type. I expect that to be straightforward, but if you have guidance on that too, it would be much appreciated.

0 0

Replies

replied on July 12, 2022 • Show version history

I don't think the connection type matters here, but there are places it does matter. For example, with a Custom Query activity, any tokens you are using within the query would use variable names with a connection string, but would use the ? character with ODBC. Since you are using the Query Data activity, that particular detail shouldn't matter.

Is the object vmTermedTeamMember the name of the table/view that you are trying to query or another table/view that is referenced within the database? Does the user that Workflow is using to connect to the database have permissions to access this vmTermedTeamMember object?

Your screenshot shows the query with four values, but only one is listing something. Is that intentional? With those included in your Query Criteria, but listing blank, it's like setting them in your WHERE statement with values equal to empty strings (WHERE EmployeeType = ''). You only need to list the items here that you are specifically searching for.

You also asked about accessing the tokens from the result set. The best way to do that is to use a "For Each Row" loop activity after your query, then within that loop, you can reference the tokens from the For Each Row activity (they'll be something like %(ForEachRow_EmployeeType) - you should be able to find them in the token picker). You do have to have a successful Test of your Query Data activity before the tokens will show in the picker - every single column in the table/view should be available as tokens since the Query Data activity works like a SELECT * statement.

1 0

replied on July 13, 2022

The problem is the same problem reported in ERROR [42S02] Invalid Object Name Error for non-dbo schema Table Lookup. The schema for the view he was querying was not dbo so the ODBC driver through the error. We recreated the view in the dbo schema and the Query Data activity worked just fine.

1 0

You are not allowed to follow up in this post.

Question

Question

invalid object name in "Query Data" - why?

Replies

Sign in to reply to this post.