Proper way to config SQL Server action Get Rows (V2) with ODATA filter for on-prem DB

[iso]

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: 

SELECT 
		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'?) :

[AndyLadwig]

@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:

• https://docs.microsoft.com/en-us/connectors/sql

[iso]

Ah yes I forgot the distinction between on-prem and in the cloud... I so wish we could be using Azure SQL but you know how it is...  😉