Business Applications, dataflow, Dataflow, Dataverse, Flow, Power Automate

Getting data from a function in an on-prem SQL server via Gateway in Power Automate

Dataflow magic in Power Automate. SQL with Power Query in Power Automate. Did you know you can do this?

CloudMinded Blog

“We are screwed!”

me at the moment of technology induced panic

That was the feeling when I saw the below error messages.

The client’s setup is an on-prem SQL server exposed to the cloud via an on-prem Gateway. It works perfectly fine for exiting Dataflow integration, but this time we needed data from a function in Power Automate.

Now problem, you can use SQL server connector, right?

Almost (but yes, if you read this post or knew before).

Set back #1

You cannot use “Execute a SQL query (V2)” with an on-prem gateway connection. Not supported!

Sure, let’s then use “Execute stored procedure (V2)”.

Set back #2

Apparently, you cannot return dataset from the function. Can call it if it was just a function or procedure without return value. But if it returns data – bad luck!

Panic mode on!

Wait, but what if…

Power Query to the rescue! Again.

View original post 159 more words

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s