cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
MarkAggar_MSFT
Frequent Visitor

Dynamic Content missing - SQL Server - Execute a Query action

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.

1 ACCEPTED SOLUTION

Accepted Solutions

 

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. 

 

View solution in original post

5 REPLIES 5
v-yamao-msft
Community Support
Community Support

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.

3.PNG

 

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

Community Support Team _ Mabel Mao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

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. 

 

View solution in original post

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?

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
Microsoft
Microsoft

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?

Helpful resources

Announcements
MPA User Group

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

MSFTBizAppsLaunchEvent

Experience what’s next for Power Virtual Agents

See the latest Power Automate innovations, updates, and demos from the Microsoft Business Applications Launch Event.

New Super Users

Meet the Power Automate Super Users!

Many congratulations to the Season 1 2021 Flownaut Crew!

Power Platform ISV STudio

Power Platform ISV Studio

ISV Studio is the go-to Power Platform destination for ISV’s to monitor & manage applications post-AppSource publish.

Users online (61,115)