Skip to main content

Notifications

Power Automate - Using Connectors
Unanswered

SQL Trigger Not working

(1) ShareShare
ReportReport
Posted on by 795

Are there any known issues at present with SQL?

 

I have a flow that fires on item created in a table but it just doesnt want to work.

The table has 21000 rows currently.

 

Gateway reports connected.

 

Ive also just tried on a new table and it also doesnt want to fire.

  • Re: SQL Trigger Not working

    It should work on azure, but it needs an IDENTITY column, this is only described in known issues, 

     

    A pity that there is no indication when designing the flow, you wait for ever before spending yout ime on internet wordering why

     

    Best regards

  • Delid4ve Profile Picture
    Delid4ve 795 on at
    Re: SQL Trigger Not working

    I have managed to devise a workaround for my scenario, you may be able to implement depending on what you need. (this is for new items created but it could be modified to allow for modified items aswell)

     

    1: Create a list (a) within sharepoint and add a column, type number to store a temporary ID

    2: create a row in this list and put the ID -1 of the SQL table in where you want to start processing from, lets say for instance we set as 0 it will start at ID 1

    2: Create a list (b) within sharepoint and add 2 columns, 1 as date only, 1 as yes/no (default as No)

    3: Create a flow on a daily recurrence at your business start time and set it to create a row in your list (b) with the current date

    4: Create a flow on a daily recurrence at your business close time and set it to update the row in your list (b) that is filtered by the current date and set the Yes/No column to Yes

    5a: Create a flow, triggered on new item created in list (b), add a do until loop and set it to terminate when Yes/No column equals true

    5b: In your do until loop: Add get row from list (a) rowID=1 (as there is only 1 row in this table use the ID for that row which should be 1 unless you made a mistake and deleted a row etc), this will get the current ID which we set as 0

    5c:Add a get rows from your sql table filtered on the field containing our current ID (Dynamic content from get row of list (a)), set this to top count 1 ie in the filter query put: ID gt DynamicfiledName

    5d: Add a compose action and in expression add length(body('Get_rows')?['value']) (ensuring you change Get_rows to the name of your get rows action)

    5e: Add a condition to check whether there are any more IDs - use the output from 5d and set is equal to 0

    5f: Under if Yes add a delay and set to say 10 minutes

    5g: Under if No Add a compose action so that we dont end up with an array and under expression paste: 

    first(body('Get_rows')?['Value'])?['ID']   (ensure you change the Get_rows part to the name of you get rows action above)
    5h: add a get row action from SQL and set the ID to the output from 5g
    5i: Now do whatever you want with the row you just got, ie: send an email, update some fields etc
    5j: Add an action - Sharepoint update item, set the list as list (a) and update the temporary ID field to the ID of the compose output in 5g
     
    Finished
     
    What this does:
    When your business opens it triggers the flow
    It then loops through the SQL table and performs your action, updating the sharepoint list with the latest ID each time it processes an item to ensure an item isnt processed twice
    The Length function checks each time that there are more rows to process
    If there are no more rows to process it delays the flow for 10 minutes and then starts the loop again.
    When you business closes it stops the flow.
     
    Obviously you need to change the times of the open and close flows so that it porcesses only when data is going to be changed in SQL, for us thats start at 0700 and finish at 1800.
     
    By doing it this way with the loop you only use 3 flow runs per day, 1 to start, 1 to finish and 1 to run the action.  If you just put the action flow on a recurrence of say every 10 minutes then you would use a fair amount of runs per day.
     
    You will have to play about if you need it for when an item is modified in SQL, it should be possible with a few extra fields in the sharepoint list but i dont require this function at present, just for new items so that i can send an email.
     
    Hope this helps
  • Dawidvh Profile Picture
    Dawidvh 1,346 on at
    Re: SQL Trigger Not working

    Would also love the on prem SQL insert to trigger flow. I would vote for this any day. Is this in the pipeline?

  • Delid4ve Profile Picture
    Delid4ve 795 on at
    Re: SQL Trigger Not working
    Thanks @v-xida-msft for clearing that up.

    They really need to make these reference links a bit more visible in flow as I did try looking for limitations but couldn't find.

    Is this on the roadmap at all?
  • v-xida-msft Profile Picture
    v-xida-msft on at
    Re: SQL Trigger Not working

    Hi @Delid4ve,

     

    Do you use On-premises SQL Server connector?

    Recently, SQL Server trigger is only supported for SQL Server in Azure. SQL Server trigger is not supported for On-premises SQL Server.

     

    More known issues and limits of SQL Connector, please check the following document:

    Known Issues and Limitations of SQL Connector

     

    Best regards,

    Kris

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,475

#2
RandyHayes Profile Picture

RandyHayes 76,287

#3
Pstork1 Profile Picture

Pstork1 64,767

Leaderboard

Featured topics