Dynamic Content missing - SQL Server - Execute a Query action

[MarkAggar_MSFT]

I have two "SQL Server - Execute a Query Actions" in the same flow.  The first one initially gave me Dynamic content, but the second one refuses to.  More troubling is that the first action does not show dynamic content any more (the dynamic content I added originally is still there), so it seems something is wrong with the flow/connector.

In particular I'm trying to get a row count from a table.

I've tried two different techniques, but neither work.

1st attempt is a simple count row e.g.

SELECT COUNT(*) AS TOTALROWS FROM TableName]

The second is:

DECLARE @rc INT
SET @rc =  (SELECT COUNT(*) AS TOTALROWS FROM TableName)
PRINT @rc

They both execute, but neither yield any dynamic content.

[v-yamao-msft]

Hi @MarkAggar_MSFT,

What’s your current situation? Are you still having the issue?

When testing it on my side, I can get dynamic contents from both the two actions “Execute a SQL query”.

A screenshot likes below.

Could you please recreate the flow from blank to see if it will work?

Please also make sure the queries you are using are correct.

If you are still having the issue, please feel free post back.

Best regards,
Mabel Mao

[MarkAggar_MSFT · Accepted solution]

Thansk for the reply.  I solved the problem with some clues from the dev team after I sent them a Fiddler trace. 

This likely applies to other actions that are using dynamic content or produce an array when there is only one line returned.

The issues are twofold. 

1. If you use dynamic content (from previous actions) in the SQL query (as I was in one of my SQL queries), the query does not resolve, and thus does not produce any dynamic content itself.  The trick is to hardcode all of the query, get the dynamic content into your subsequent actions and then replace the hardcoded values in the SQL query with dynamic content from the previous actions.
2. The output from the count() does not produce anything that can be used in a variable directly.  This is because the SQL query returns an array, even when there is only one line returned.  My method is to initialize a variable, do the query, add an ‘apply to each’ and then, within the ‘apply to each’, add an ‘increment variable’ using the output from the ‘apply to each’ (this obviously only works for numbers).
   • The 'Print' method above yields no dynamic content that can be used in any form.  Which is a shame because in theory I should have been able to put the output of this directly into an initialize variable action (as it's not an array)
   • The alternative to using the approach above (if your one line output is a string) is to use Select (from the query results, with a map to the column you want to use) and Join (from the Select action ouput) actions and use the output from the Join in a variable.  Same number of steps as above. 

[kkluszynski]

I am having the same problem.  I don't understand what is meant by "The trick is to hardcode all of the query, get the dynamic content into your subsequent actions and then replace the hardcoded values in the SQL query with dynamic content from the previous actions."  Where does the hardcoding take place?  How do I do this?

[MarkAggar_MSFT]

For instance, if you had a query that was a simple

SELECT * FROM TABLENAME 

WHERE COLNAME = '[Flow Dynamic Content]'

you should temporarily hardcode the [Flow Dynamic Content] to a regular string

SELECT * FROM TABLENAME 

WHERE COLNAME = 'String'

It can literally be anything, just needs to be some text or number.

Then you'll be able to see the COLNAME column as dynamic content in subsequent flow actions, etc.

Once you've added those Flow dynamic content(s) into your subsequent actions, go back to your SQL (or whatever) action and put the Flow dynamic content variables back in where they were originally.

[nbrady]

Experiencing a similar issue too, specifically related to Ask in Power Apps with SQL Server connection. I tried Ask in Power Apps within the 'Execute a SQL query (V2)' query where ID = 'Ask in Power Apps'

but no new steps return dynamic content from SQL.

I even initialized a variable, setting it as the dynamic value I pass through Power Apps, then use the int Variable instead of 'Ask in Power Apps' within the query to return the Result Set of the query.

Still nothing.

Dynamic Content only returns in subsequent steps if I hardcode the query. Is there another solution?