Skip to main content

Notifications

Power Automate - Using Connectors
Unanswered

MS Flow Trigger for onPremise SQl Server

(0) ShareShare
ReportReport
Posted on by 6

Hi All,

 

Can we trigger MS flow on Insert/Update row in Onpremise SQL Server. I have read on some older blogs that trigger doesn't work for onPremise sql server but on latest Flow documentation it not mentioned anywhere .[https://docs.microsoft.com/en-gb/connectors/sql/#known-issues-and-limitations]

 

Actually i installed SQL Server on my local system and configured gateway. I can trigger MS flow on Insert/Update row for onpremise Sql server. Now Client  has shared a Sql Server connecction, i configured MS flow to trigger for this connection but its not triggering at all. After going through all the blogs i got confused whether trigger suppose to work or not for Onpremise sql server. Any help would be appriciated.  

  • SarfrazKhan Profile Picture
    SarfrazKhan 29 on at
    Re: MS Flow Trigger for onPremise SQl Server

    Couple of different ways to trigger a flow from an on-premise CRM installation.

    1. Create a background workflow that runs on the trigger event. e.g. Run on create of contact. In the flow, select the SQL trigger called "When and item is created V2" and select the AsyncOperationBase table as the triggering table. This table creates a record everytime a background workflow runs. Once you have this set, click on the ellipsis on the block and put in your triggering conditions like this:

    @equals(triggerBody()?['Name'],'Name of Workflow')

    @equals(triggerBody()?['MessageName'],'ExecuteWorkflow')

    2. Create a flow that uses "When an HTTP Request is Received" trigger. Then download ZooY's Dynamics 365 Tools. Create a workflow on the triggering event and put the PostJSON workflow activity from his tools. Put in the address generated from the flow in the Service URL and the JSON body you want to send to the flow, e.g. { "mobilephone": "555-555-5555", "RecordUrl":"https://crm.contoso.com" }.

    If you want to sent the record id this way, you can add the recordUrl with the body or use Damien Rasko's Workflow Tools to get the record id before making the flow call.

    Tools:

    https://github.com/ZooY/Dynamics365.Tools

    https://github.com/demianrasko/Dynamics-365-Workflow-Tools

     

  • moutinhoabreu Profile Picture
    moutinhoabreu 170 on at
    Re: MS Flow Trigger for onPremise SQl Server

    We work with a SQL on premises and I can also confirm we are able to trigger flow on a "new record" for every table inside the SQL.

    Super easy and we've a 2016 version installed. In fact we do have several flows working with this trigger.

     

    Regarding triggering on "update", we were never able to achieve it !!! looking at recent documentation there is no indication about on premises limitation. They do mention, you just need to assure the table has:

     

    "A Primary Key is required for the following operations:

    • GetItem
    • UpdateItem
    • DeleteItem"

     The tables we wish to use, we know the primary key, but flow doesn't identify a Primary key.

     

    So.. 

     

  • MickThornton Profile Picture
    MickThornton 50 on at
    Re: MS Flow Trigger for onPremise SQl Server

    @Anonymous is correct. I can also confirm that Flow connecting to SQL OnPrem through data gateway works for the create or add record to a table.  Updates to a record does not work (as of June 2019) through the data gateway to SQL OnPrem.  

     

    Example: If status = "Under Review" for a record and a flow is set to send an email when the status = "Under Review". 

    Could a person use the flow to "ping" the database every 15 minutes and look for a change in a field within a record in a table?  Or would that send an email every 15 minutes that the field currently has a specific value being looked for?  

     

     

  • Re: MS Flow Trigger for onPremise SQl Server

    I have on prem triggers working here with SQL 2008, but it only works for add not modify, and only with a table that has an identity.

  • moutinhoabreu Profile Picture
    moutinhoabreu 170 on at
    Re: MS Flow Trigger for onPremise SQl Server

    I would also love to know if it's already working.

     


  • A8Tony Profile Picture
    A8Tony 66 on at
    Re: MS Flow Trigger for onPremise SQl Server

    Hi CRMSample,

     

    I've tried a quick test this morning but have not successfully triggered a flow from a record being created.

     

    Can you confirm that this is possible using the On Premise Data Gateway? And if so, did you have to make any specific settings on the SQL database configuration to allow this?

     

    Thanks,

    Tony

  • A8Tony Profile Picture
    A8Tony 66 on at
    Re: MS Flow Trigger for onPremise SQl Server
    Thanks for this CRM Sample, I'll give this a go next week 👍
  • CRMSample Profile Picture
    CRMSample 6 on at
    Re: MS Flow Trigger for onPremise SQl Server

    Hi A8Tony,

     

    Actually Flow will trigger for On premise server as well but only for the latest version of sql server. I have tested it with the SQL version 2017. For earlier version it wont work (not sure about 2016)

     

  • A8Tony Profile Picture
    A8Tony 66 on at
    Re: MS Flow Trigger for onPremise SQl Server

    It is my understanding that it is not currently possible to trigger flow from an on-premise SQL server. The documentation you refer to in your last link describes functionality which is available to an SQL Azure database.

     

    It is something I'm absolutely waiting for, as I have clients who use on-premise SQL databases (often multiple) and this would allow me to do even more with Flow 

  • CRMSample Profile Picture
    CRMSample 6 on at
    Re: MS Flow Trigger for onPremise SQl Server

    Thanks for the reply @v-yuazh-msft.

    Actually before posting this qus, i tested it at my end using onpremise data gateway and it worked fine for me. But the issue i am facing is, third party vendors are managing onpremise database. They have configured the onpremise gateway and shared the connection with service account, using that service account i have configured the MS flow to trigger on insert/update of sql table. Now when we do a insert in sql table, MS flow doesn't trigger. Its not showing any failed or Error in run history. 

    but I can see many "Skipped entry" in Checks(no new data) in run history section. Can you please help me on this issue.

    After going through some blogs i was wondering whether trigger suppose to work or not for onpremise sql server.[https://flow.microsoft.com/en-us/blog/introducing-triggers-in-the-sql-connector/]

    Since you have confirmed it, can you please advice me on some troubleshooting steps. Just to mention i am new to MS flow and trying to get all the helps to solve this issue. Many thanks !

      

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

Microsoft Kickstarter Events…

Register for Microsoft Kickstarter Events…

Announcing Our 2025 Season 1 Super Users!

A new season of Super Users has arrived, and we are so grateful for the daily…

Announcing Forum Attachment Improvements!

We're excited to announce that attachments for replies in forums and improved…

Leaderboard

#1
WarrenBelz Profile Picture

WarrenBelz 145,691

#2
RandyHayes Profile Picture

RandyHayes 76,287

#3
Pstork1 Profile Picture

Pstork1 65,019

Leaderboard

Featured topics