none
how to read and write to Azure SQL from MS Flow SharePoint Online RRS feed

All replies

  • Hi All,

    I need to read and write in a azure sql table from SharePoint Online list. How to achieve this?

    Good day SamDany,

    Since I have no idea who ask the question and what you already know, let me start from the basic.

    Reading, adding, and manipulating existing data is done using queries. In the case of Azure SQL Database it is done using queries in Transact SQL language.

    Once the network, host, server, and database are well configured (for example firewall configuration), we can connect the database from any application in the same way you connect to other servers. Usually the only difference is the "connection string" which is used for the connection.

    In the case of SharePoint Online there are some preparation which you need to do in the SharePoint Online before connecting to the database. In general you do not write your queries directly since the SharePoint  app do it for you behind the scenes, at least for the common tasks.

    The following tutorial will help you step-by-step in the task
    Make an External List from a SQL Azure table with Business Connectivity Services and Secure Store


    signature   Ronen Ariely
     [Personal Site]    [Blog]    [Facebook]    [Linkedin]


    Wednesday, November 20, 2019 7:00 AM
    Moderator
  • Wanted to add that Microsoft Flow is a business workflow designer and is triggered by events. I do not see how these event can be written to a data source, if that is the intent here. Please see the SharePoint Maven site for more information: https://sharepointmaven.com/what-is-microsoft-flow/

    Please let us know if you have additional questions.

    Regards,

    Mike

    Thursday, November 28, 2019 1:43 AM
    Moderator
  • In addition to the responses posted above, here is a detailed step by step tutorial on connecting Sharepoint online to SQL database and creating a page with CRUD operations on the data.

    Friday, November 29, 2019 4:10 AM
    Moderator
  • I dont want to use BCS because of its limitations. Is there any other alternative?
    Monday, December 2, 2019 7:09 PM
  • Hi Sam,

    There is not much to work with here in terms of what your use case requires other than the read/write to an SQL table. There is a single Microsoft Flow template that demonstrates working with SQL: When an existing list item is modified, update a SQL row

    The full set of Microsoft Flow templates is available here. I also found the below Stack Overflow answer pertaining to creating an SQL Server Connector: 


    You can use Microsoft Flow (https://flow.microsoft.com) in order to create a programmed flow with an SQL Server connector. Then in the connector you set the SQL Azure server, database name, username and password.

    SQL Server connector

    There are many options but the ones that you can use to run a T-SQL query daily are these:

    SQL Connector options

    • Execute a SQL Query
    • Execute stored procedure

    You can also edit your connection info in Data --> Connections menu.

    Does this answer your question? You could create two stored prcedures, one for the WRITE operation and the other for the READ operation and call each respectively through the Microsoft Flow SQL Connector.

    Regards,

    Mike


    Tuesday, December 10, 2019 7:09 PM
    Moderator
  • Just checking in to see if the above answer(s) helped.

     

    If this answers your query, do click “Mark as Answer” and Up-Vote for the same which might be beneficial to other community members reading this thread .

    And, if you have any further query do let us know. 

    Saturday, December 14, 2019 8:21 AM
    Moderator