Logic App - Store query result from Azure SQL in to Sharepoint and retrieve back the link

Pratim Das, Partha C 306 Reputation points
2024-04-22T03:33:02.6+00:00

Hi All,

I've very basic requirement but unable to chalk it out, how to do it.

  1. I have one standard logic app workflow. It will be http triggered.
  2. Once triggered, it will run a stored procedure on one Azuer DB table and store the result as JSON in share point.
  3. Logic App will fetch the URL of the file and send as link within an email

Please let me know how I can achieve it.

Regards,

Partha

Azure SQL Database
Azure Logic Apps
Azure Logic Apps
An Azure service that automates the access and use of data across clouds without writing code.
2,904 questions
SharePoint
SharePoint
A group of Microsoft Products and technologies used for sharing and managing content, knowledge, and applications.
9,896 questions
0 comments No comments
{count} votes

Accepted answer
  1. Patchfox 3,791 Reputation points
    2024-04-22T04:11:59.1033333+00:00

    Hi Pratim Das, Partha C I want to help you with this question.

    To verify I understand your question correctly, you want to create a logic app that will be triggered by an HTTP request, execute a stored procedure afterward, store the result as JSON in a SharePoint list or something, and fetch the link of it to send it via email at the end. Is that right?

    These are the steps I would do here:

    HTTP Triggered Logic App: Create a new Logic App in your Azure portal. Set up an HTTP trigger to start the workflow when an HTTP request is received.

    Connect to Azure SQL Database: Add an action to connect to your Azure SQL Database. Provide the necessary connection details (server name, database name, credentials).

    https://learn.microsoft.com/en-us/connectors/sql/

    Execute Stored Procedure: Add an action to execute your stored procedure. Specify the stored procedure name and any required parameters.

    https://learn.microsoft.com/en-us/connectors/sql/#execute-stored-procedure-(v2)

    Format Result as JSON: Use the FOR JSON PATH clause in your SQL query to format the result as JSON directly within the query.

    SELECT CustomerName AS Name, PhoneNumber AS [Contact.Phone], FaxNumber AS [Contact.Fax]
    FROM Sales.Customers
    WHERE CustomerID = 931
    FOR JSON PATH, WITHOUT_ARRAY_WRAPPER
    
    

    This will return a single JSON object instead of an array.

    Store JSON in SharePoint: Add an action to create an item in SharePoint.

    Use the formatted JSON result from the previous step as the content of the item.

    https://learn.microsoft.com/en-us/connectors/sharepointonline/#create-item

    Get File URL: Retrieve the URL of the created item in SharePoint.

    You can use the output from the previous action to get the file URL.

    Send Email with File Link: Add an action to send an email. Include the SharePoint file URL in the email body.https://learn.microsoft.com/en-us/connectors/office365/#send-an-email-(v2)

    I hope this will help you to create the logic app


    If the reply was helpful, please don’t forget to upvote or accept it as an answer, thank you!


0 additional answers

Sort by: Most helpful