locked
Long-running stored procedure on logicapp RRS feed

  • Question

  • Hi, 

    There is a posibility to execute stored procedure which will be running more than 120s? Stored procedure is executed on Azure SQL server. 

    I figured out that there are limits and I shoud use asyncPattern, but how to turn on it on Azure SQL server? :) 

    Regards, 
    Mike

    Thursday, November 22, 2018 10:56 AM

All replies

  • Hi MIke,

    You can probably take a Look into the below.May be it can help you.

    https://connectedcircuits.blog/2018/07/04/using-logic-app-webhooks-to-execute-long-running-sql-queries/

    it can be done in combination of web-hook API Connector and Azure Functions.


    Sujith

    Thursday, November 22, 2018 11:26 AM
  • Thank you for reply. I read that article, but it doesn't fit in 100% to my "perfect" solution.

    I was hoping there is solution to use only logicapps and stored procedures execution without creation of azure functions. 
    Thursday, November 22, 2018 12:24 PM
  • Hi Mike ,

    There are multiple ways you can perform read/write operation with sql server . One of the way is to work with pagination and within the loop 

    https://social.technet.microsoft.com/wiki/contents/articles/40060.sql-pagination-for-bulk-data-transfer-with-logic-apps.aspx

    If its a big data dealing then you also might consider combination of logic apps ,data factory events etc .



    If this answers your question please mark it accordingly. If this post is helpful, please vote as helpful by clicking the upward arrow mark next to my reply

    Friday, November 23, 2018 8:34 AM
  • Hey Mike - any updates on this?
    Monday, November 26, 2018 11:51 PM
  • Hi,

    You can do it in another way using Run books. Please find the below

    https://connectedcircuits.blog/2018/11/25/using-azure-logic-app-and-an-automation-runbook-to-execute-a-long-running-sql-stored-procedure/


    Sujith

    Tuesday, November 27, 2018 8:06 AM
  • @Abhishek
    Thank you, but I don't have to read/write in this particular case.
    I can fire stored procedure and forget about it.

    But that could be helpful for different usecase. Thanks! 

    @Sujith
    It's interesting and sounds good. 
    Thank you

    @Mike
    This topic gave me an idea.
    I created Azure Function which is triggered by EventBus and it call stored procedures with SqlCommand ;)

    Tuesday, November 27, 2018 1:30 PM
  • Hi Michal ,

    If this is fire and forget thing than why not to use data factory for this !! This will give you capability of big data ingestion and can work well with event grid trigger 

    I have implemented similar usecase this way ,you can modify the design as per your requirement 

    https://medium.com/@abhishekcskumar/cosmos-graph-database-big-data-processing-with-azure-data-factory-functions-and-event-grid-31b056a285d7


    If this answers your question please mark it accordingly. If this post is helpful, please vote as helpful by clicking the upward arrow mark next to my reply

    Wednesday, November 28, 2018 1:04 AM
  • If you can create a sql JOB, or elastic job in azure, the following solution worked for me and it's simple:

    1-create a stored procedure that starts a job: exec msdb.dbo.[sp_start_job] 'jobname'

    2-create a job named 'jobname' that has 1 step in it that executes your long running stored procedure: exec sp_longrunning

    3-instead of calling the long running stored procedure from your logic app, call the stored proc created in step 1, which will start the job and return immediately, the job will then start the long running stored proc.

    Hope this helps,

    Talal


    • Edited by TNehme Friday, April 12, 2019 7:58 PM typo
    Friday, April 12, 2019 6:59 PM