How can I execute a stored procedure from my cloud workflow?

(0) Share

Report

Posted on by ADumith

277

Hello everyone,

I've realized that connecting my cloud workflow to the database necessitates using the on-on-premises data gateway. Unfortunately, I've also discovered a limitation: the inability to execute queries from a cloud-based workflow to an on-prem database.

While one solution involves leveraging processes stored within the database, I'm concerned about potential gateway failures when attempting to connect to my on-prem database server. If such a scenario occurs, I'm seeking alternative methods to establish and maintain the connection between my workflow and the database.

The workflow's functionality is dependent on monitoring a specific table on the server and initiating actions when a new record is created. This mandates a continuous connection to the database server.

Any suggestions on ensuring this uninterrupted connection would be greatly appreciated.

Thank you.

Categories:

Using connectors

I have the same question (0)

All responses (7)

Answers (1)

Pstork1 68,697 Most Valuable Professional on at

Like (0)

Report

There is an action built in to the SQL connector that will let you execute a SQL stored procedure.

But why do you say that you can't execute a SQL query from a cloud flow? There is also an action in the SQL connector that lets you do that.

Both of those work on a connection through an on-premises gateway. If you are concerned about redundancy you can create a pool of gateways in case one fails.

Was this reply helpful? Yes No
Agnius Bartninkas Most Valuable Professional on at

Like (0)

Report

@Pstork1 , the Execute SQL statement does not support on-premise SQL server. That's clearly stated in the documentation for the connector.

@ADumith , the gateway works fine for monitoring databases and having a constant connection. We have several clients relying on this functionality, and have yet to face any issues with it.

-------------------------------------------------------------------------

If I have answered your question, please mark it as the preferred solution. If you like my response, please give it a Thumbs Up.

I also provide paid consultancy and development services using Power Automate. If you're interested, DM me and we can discuss it.

Was this reply helpful? Yes No
Pstork1 68,697 Most Valuable Professional on at

Like (0)

Report

You are correct about the query. I had forgotten that. But the Stored Procedure action is supported on-premises. I normally prefer using Stored Procedures anyway if you have access to create them.

Was this reply helpful? Yes No
ADumith 277 on at

Like (0)

Report

Hello guys,

Thank you so much for your reply.

So, what type of authentication do you recommend me?
Because no matter which one I select it's asking me to indicate which data gateway I am going to use for the connection.

Thank you again,

Was this reply helpful? Yes No
Verified answer

Pstork1 68,697 Most Valuable Professional on at

Like (1)

Report

Any of these will require an on-premises gateway. There is no way to run any kind of cloud flow against on-premises SQL without a gateway. But as I mentioned in my first reply if you are concerned about redundancy of the gateway you can create a gateway cluster that will keep working if one gateway goes down.

Was this reply helpful? Yes No
Agnius Bartninkas Most Valuable Professional on at

Like (0)

Report

Yes. That's exactly why I recommended they use a Stored Procedure under a different thread, as that is the best way to do what they want to do on an on-prem SQL server.

@ADumith , as @Pstork1 mentioned, you will need a gateway anyway. No other way to connect to an on-prem database in PA cloud. The auth method of your choice really depends on the authentication settings on the server. The most frequent cases are either using SQL Server Authentication (where you provide a user name and a password), or Windows Authentication (where your windows user is used for auth). It usually is set up on the server side, and it's not like you can choose the one you prefer the most (unless you set up the server).

So, use the one you normally use when running queries to the database.

-------------------------------------------------------------------------
If I have answered your question, please mark it as the preferred solution. If you like my response, please give it a Thumbs Up.

I also provide paid consultancy and development services using Power Automate. If you're interested, DM me and we can discuss it.

Was this reply helpful? Yes No
ADumith 277 on at

Like (0)

Report

Thank you gentlemen for clarifying my doubts.

Was this reply helpful? Yes No