cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Kudo Collector
Kudo Collector

Insert into SQL table with active triggers implemented

Hi guys,

 

I can successfuly get flow to insert a row into one of my SQL tables. Now the issue I have is that I need to set up a trigger on this table to get the same data sent pover to  a different table on a different Server.

 

Since implementing the trigger Flow will not insert into the original table:

 

error:

 

"status": 400,

  "message": "Invalid Mashup Expression using supplied values.\r\n     inner exception: Microsoft SQL: The target table 'dbo.TABLE' of the DML statement cannot have any enabled triggers if the statement contains an OUTPUT clause without INTO clause.",

  "source": "sqlconnectionprovider-westeurope.am2-ase-001.p.azurewebsites.net"

 

Is there any way around this please?

 

For the error I suspect that the SQL Flow generates in the back end to get the data into my table is the issue by not using the into clause when using the output clause?

 

many thanks

 

Lee

21 REPLIES 21

Hi @SmartMeter 

You are right!  I should have clarified the context. I was catching up on this thread and the workarounds provided. The old thread was meant to refer to the workarounds which were not there then. Sometimes in a hurry to post the solution, we fail to explain things properly. Appreciate you pointing this out.

 

The problem is very real - and the most frustrating part is that there is no easy workaround.  The ones suggested earlier (Stored Procs, or remove the SQL Triggers) are very invasive in that it requires one to modify the SQL Server Database.  One of the main motivation for us to add support for the native SQL query execution was essentially to get around problems like this.  It does take a little bit of understanding basic SQL query though. 

 

And I actually did try this out before I responded.  The reason I didn't include the screenshot is because I don't see a lot of value. But this is how it looks like anyway:

 
 
 

image.png

 

HTH

Sameer

 

Thank you Sameer for taking my comment professionally as intended, and for posting a most helpful response for others who will encounter the issue in time. I respect your answer, and I feel for your side of the equation. This problem was a real time killer in my case, but knowing SQL intimately, it was pretty easy to dig my way out of trouble. A lot of users are not SQL savvy, and they need all the help our mothership is willing to provide here. As for the rest of us "users" please don't be rude to Microsoft, this is a known issue and they will eventually fix it, but realize it is making PowerAutomate essentially run code on your database so it's certainly a challenge on their side to provide a secured means to do this.  We are asking them to "grant execute on * to MyGatewayAccount" which is quite insane to secure when you think about it. Thanks again for accepting professional criticism. (Now go cure Coronavirus !)

Peace.

Helpful resources

Announcements
PP Bootcamp Carousel

Global Power Platform Bootcamp

Dive into the Power Platform stack with hands-on sessions and labs, virtually delivered to you by experts and community leaders.

secondImage

Power Platform Community Conference On Demand

Watch Nick Doelman's session from the 2020 Power Platform Community Conference on demand!

MPA Community Blog

Power Automate Community Blog

Check out the community blog page where you can find valuable learning material from community and product team members!

Top Solution Authors
Top Kudoed Authors
Users online (7,924)