You’re offline. This is a read only version of the page.
I am sure I must not have been the only fool who has struggled like crazy to get this to work, so to answer all future questions re this, here it is.
Let's say you want to send the following basic SELECT statement to a SQL Server DB:
Share
Report
250SELECT
rev_code
, FaultId
FROM [schema].[table_name]
WHERE
rev_code = <some_number> AND
FaultId = <some_number>
;
Here is a screenshot of how to set up the SQL Server Get Rows (V2) action:
Note that the variable names (in purple) are AND must be surrounded by single quotes! So this: 'var_name' AND NOT : "var_name" even though both variables are of type integer.
And finally, if you need to use some aggregation in your select query, for ex:
SELECT MAX(rev_code) AS max_rev_code
FROM [schema].[table_name]
;
This is how you specify it in the SQL Server Get Rows (V2) action using ODATA ( and by the way, Microsoft, if the big idea behind Power Automate is to have a no-code platform, then why on Earth do users have to learn yet another syntax--ODATA--to emit SQL statements? So, if they are going to 'code' in ODATA, why not just let them enter straight SQL? One step less is better. But it is still coding, so then why not have a GUI where you just click on widgets to build your SQL statement visually and have the engine translate that on the backend? Wouldn't that qualify more as 'no-code'?) :
@iso , thanks for posting this! This is a great clarification. These OData filter options are hidden under the 'advanced options' menu for exactly the reasons you specified 🙂 . If you are not using on-premises SQL server, you may be able to leverage the Execute a SQL query (V2) action to execute an ad-hoc SQL query as you suggested. Alternatively, you could try writing your query as a stored procedure on your server and then execute it using the Execute stored procedure (V2) action.
For more information on the triggers, actions, and limitations of the SQL server connector, please refer to the official connector documentation:
All responses (
Answers (
