cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
RussellGove Kudo Kingpin
Kudo Kingpin

Execute a SQL Query

Hi,

I am trying to use the Execute a SQL Query as shown below:

Capture3.PNG

The flow fails with a message

Capture2.PNG


Capture.PNG\

 

How can I get the query to run?

 

7 REPLIES 7
Community Support
Community Support

Re: Execute a SQL Query

Hi @RussellGove,

 

The 502 Bad Gateway error is caused when a network error between servers on the internet. The failure is temporary or transient, please try to resubmit to try the flow again.

Besides, clear your browser’s cache and delete your browser’s cookies would always work.

Please also try to run the browser in a safe mode to see if it will work.

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

 

Best regards,

Mabel

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.
RussellGove Kudo Kingpin
Kudo Kingpin

Re: Execute a SQL Query with formal parameters

Hi,

I dont think its a network issue. If I get rid of the formalParemter and use straight SQL it works fine. At the bottom of the post you  can see the error 'must decalare parameter giftyear;' . 

 

This question is really about how to use formal parameters in a SQL Query

Highlighted
ColonelJustice
New Member

Re: Execute a SQL Query with formal parameters

Hi, you have to declare the formalparameter as variable within the SQL query. So: DECLARE @userid VARCHAR(10); (or whatever value type) SELECT FROM a WHERE userid = @userid This way the parameters can be passed onto the query with the corresponding variable name. Best regards
Administrator
Administrator

Re: Execute a SQL Query

Hey, @RussellGove!

Thank you for posting on the Flow Community Forum! Have you had an opportunity to apply any of these recommendations to adapt your Flow? If yes, and you find that solution to be satisfactory, please go ahead and click “Accept as Solution” so that this thread will be marked for other users to easily identify!

Thank you for being an active member of the Flow Community!

-Gabriel
Flow Community Manager

-Gabriel
Microsoft Power Automate Community Manager
Are YOU a member of your local Power Automate User Group?
Fill out This Form to claim your Community User Group Member Badge!
stjef
New Member

Re: Execute a SQL Query

I can confirm that if you have the DECLARE section for each variable in 'formalParameters' then the query works as expected.

 

It'd be good if it was made apparent in the UI or in the error message that this DECLARE is required, as I assumed formal parameters were simply broken until I read this post.

 

It's not easy to find the documentation for Execute a SQL Query either - maybe this does mention this requirement. Could you point us to it?

 

EDIT: Actually it is not working. There is no error given but the SQL doesn't actually produce the result I'm expecting.

Is there any documentation for formalParameters? I'm very much at a loss as how to use the feature at all.

 

 

 

ColonelJustice
New Member

Re: Execute a SQL Query

Hi stjef,

 

can you elaborate on what exactly is not working? Is the SQL query returning unexpected results, no results at all or do you have issues accessing / processing the resultset later in PowerApps etc. ?

 

Thanks and best regards

Florian

SethC
New Member

Re: Execute a SQL Query

I can also confirm that adding the variable declaration fixed the syntax issue and allowed the step to process succcessfully.  However, I also got unexpected results in that the dataset came back empty (and should have returned 1 row based on the parameter)

 

Turing on auditing on my SQL instance showed 2 interesting events:

  •  One BATCH COMPLETED event that contained the DECLARE & SELECT statements, but with no value for the supplied parameter.  When executed in SSMS, this query returned no rows
  •  One RPC COMPLETED event where the entire query was wrapped in an "exec sp_executesql" command.  When executed in SSMS, this returned an error that the variable had already been declared. 

 

I agree that this function is poorly documented, which makes it difficult to understand if we're even using it correctly.  In fact, the current specification here (https://docs.microsoft.com/en-us/connectors/sql/#execute-a-sql-query-(v2)) doesn't even mention formalParameters as a valid parameter. 

Helpful resources

Announcements
firstImage

New Ranks and Rank Icons released on April 21!

The time has come: We are finally able to share more details on the brand-new ranks coming to the Power Automate Community!

firstImage

Now Live: Power Virtual Agents Community!

We are excited to announce the launch of Power Virtual Agents Community. Check it out now!

firstImage

New & Improved Power Automate Community Cookbook

We've updated and improved the layout and uploading format of the Power Automate Cookbook!

thirdimage

Power Automate Community User Group Member Badge

Fill out a quick form to claim your user group badge now!

sixthImage

Community Summit North America

The top training and networking event across the globe for Microsoft Business Applications

Top Solution Authors
Top Kudoed Authors
Users online (8,269)