{"id":2201,"date":"2022-12-09T14:28:26","date_gmt":"2022-12-09T13:28:26","guid":{"rendered":"https:\/\/blog.kodono.info\/wordpress\/?p=2201"},"modified":"2022-12-09T15:20:11","modified_gmt":"2022-12-09T14:20:11","slug":"power-automate-execute-a-sql-query-via-on-promise-gateway","status":"publish","type":"post","link":"https:\/\/blog.kodono.info\/wordpress\/2022\/12\/09\/power-automate-execute-a-sql-query-via-on-promise-gateway\/","title":{"rendered":"Power Automate: execute a SQL Query via On-Promise Gateway"},"content":{"rendered":"

In Power Automate, when you want to connect to a SQL Server and if you have a On-Promise Gateway, then you cannot use the command “Execute a SQL Query”<\/b> because it will say it’s not currently supported.<\/p>\n

There is a workaround with “Transform data using Power Query”<\/b> (ATTENTION: you cannot load it from a flow from a Solution\u2026 you’ll have to go to your Flows and edit the flow from there):
\n\"\"<\/a><\/p>\n

Let’s say we have 3 tables: ITEM_CATALOG, CATALOG and CURRENCY. We want to join them and filter them based on a variable found previously in our flow.<\/p>\n

First, we can define our where<\/code>. Here I have several values that I want to test using a IN<\/code>:
\n\"\"<\/a><\/p>\n

I create a string with my different values separated by a coma.<\/p>\n

Next, we can open the Power Query editor:
\n\"\"<\/a><\/p>\n

In the interface, we choose the 3 tables we need to merge and we add a parameter called “where”:
\n\"\"<\/a><\/p>\n

We rename it to “where” and leave the default settings:
\n\"\"<\/a><\/p>\n

Then we use the “Advance Editor”<\/b>:
\n\"\"<\/a><\/p>\n

And we wrote the below:<\/p>\n

\r\nlet\r\n  where = Text.Split( \"@{variables('where')}\" , \",\")\r\nin\r\n  where\r\n<\/pre>\n
It means we want to split the variable “where” coming from the flow, based on the coma separator:
\n\"\"<\/a><\/p>\n
We can now merge the tables and add a filter:
\n\"\"<\/a><\/p>\n
And when the step to filter is here, we select “in”<\/b> and our query:
\n\"\"<\/a><\/p>\n
Last step is to “Enable Load”<\/b> to make sure this is what the operation will return to our flow:
\n\"\"<\/a><\/p>\n
You can run it to test and see if it works.<\/p>\n
Then, to get the output from it, we’ll use a “Parse JSON”<\/b>\u2026 The schema is probably something like:<\/p>\n
\r\n{\r\n    \"type\": \"object\",\r\n    \"properties\": {\r\n        \"resultType\": {\r\n            \"type\": \"string\"\r\n        },\r\n        \"value\": {\r\n            \"type\": \"array\",\r\n            \"items\": {\r\n                \"type\": \"object\",\r\n                \"properties\": {\r\n                    \"COLUMN_A\": {\r\n                        \"type\": \"string\"\r\n                    },\r\n                    \"COLUMN_B\": {\r\n                        \"type\": \"integer\"\r\n                    },\r\n                    \"COLUMN_C\": {\r\n                        \"type\": \"string\"\r\n                    }\r\n                },\r\n                \"required\": [\r\n                    \"COLUMN_A\",\r\n                    \"COLUMN_B\",\r\n                    \"COLUMN_C\"\r\n                ]\r\n            }\r\n        }\r\n    }\r\n}\r\n<\/pre>\n
You may need to make several tries in order to find the correct schema. You can also use the “Generate from sample”<\/b> by pasting the data from the previous step:
\n\"\"<\/a><\/p>\n
We use “value”<\/b> in the loop:
\n\"\"<\/a><\/p>\n
And then we can access our columns:
\n\"\"<\/a><\/p>\n","protected":false},"excerpt":{"rendered":"
In Power Automate, when you want to connect to a SQL Server and if you have a On-Promise Gateway, then you cannot use the command “Execute a SQL Query” because it will say it’s not currently supported. There is a workaround with “Transform data using Power Query” (ATTENTION: you cannot load it from a flow […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_coblocks_attr":"","_coblocks_dimensions":"","_coblocks_responsive_height":"","_coblocks_accordion_ie_support":"","hide_page_title":"","footnotes":""},"categories":[1,170,20],"tags":[146,123,155],"class_list":["post-2201","post","type-post","status-publish","format-standard","hentry","category-divers","category-english","category-niveau-expert","tag-divers","tag-english","tag-niveau-expert"],"_links":{"self":[{"href":"https:\/\/blog.kodono.info\/wordpress\/wp-json\/wp\/v2\/posts\/2201","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/blog.kodono.info\/wordpress\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/blog.kodono.info\/wordpress\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/blog.kodono.info\/wordpress\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/blog.kodono.info\/wordpress\/wp-json\/wp\/v2\/comments?post=2201"}],"version-history":[{"count":6,"href":"https:\/\/blog.kodono.info\/wordpress\/wp-json\/wp\/v2\/posts\/2201\/revisions"}],"predecessor-version":[{"id":2220,"href":"https:\/\/blog.kodono.info\/wordpress\/wp-json\/wp\/v2\/posts\/2201\/revisions\/2220"}],"wp:attachment":[{"href":"https:\/\/blog.kodono.info\/wordpress\/wp-json\/wp\/v2\/media?parent=2201"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/blog.kodono.info\/wordpress\/wp-json\/wp\/v2\/categories?post=2201"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/blog.kodono.info\/wordpress\/wp-json\/wp\/v2\/tags?post=2201"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}