How to run a stored proc in an on prem sql server using azure logic apps standard

Question

Hi,

I've seen so many blogs on Microsoft but it is no where clear on "Whether we need to use On Prem Data Gateway to run a stored proc on an on prem sql server when using Azure Logic Apps Standard"?

it is a bit confusing in the link which says - On Prem Gateway is needed for both Consumption + Standard tier logic apps. Can you please list out the steps that we need to perform in case of Logic App Standard connecting to an on prem sql server to run a stored proc?

Thanks,

Answer 1

Hello Pavan Puligandla,

Thanks for reaching out.

.Create the Logic App Click on +Create a resource and enter Logic App in the Search box Click on Create Logic App and enter the details and Click Review+Create and the Create

As a first step click on Recurrence trigger to open the below shown Logic App Designer

Enter the Interval and Frequency , to select a Time Zone or Start Time Click on 'Add new Parameter; and choose the Time Zone , for this example I selected Eastern Time as the Time Zone and no Start Time ( the logic app will run every 3 minutes once its published

Select the Time Zone you wish to schedule the logic app and click on the + sign to add a new Action to it. In the new action, type 'variable' and click on Variables

Click on Initialize variable and enter variable name as empExperience , type as Float and default value as 0 , this variable will hold the result set from the Stored procedure

Add a new action and type SQL server in the search box to show all the available actions related to SQL server and then click on the SQL Server Under the SQL Server Actions select 'Execute Stored Procedure (V2)'

Select the appropriate Authentication Type to connect to the SQL Server , in this example I used SQL Server Authentication

Enter the SQL Server connection details and select the gateway that we created in the third step ( test-gw) and click Create

In the next step we will select the stored procedure that we created earlier and provide the input parameter (empID) value to it.

If this answer was helpful, please click "Accept the answer" and mark Yes, as this can help other community members.

If you have any other questions or are still experiencing issues, feel free to ask in the "comments" section, and I'd be happy to help.