SQL Patch Errors Server Response: failed: Microsoft SQL: The target table ‘’ of the DML statement cannot have any enabled triggers if the statement contains an OUTPUT clause without INTO clause

[SomeoneAwesome]

Hi,

I am trying to patch data to SQL, i was previously getting bad gateway errors and was advised i need to create a onpremise gateway, i have done so now however whenever i try to patch to this one particular datatable i get errors

The current error is this

Server Response: TableName failed: Microsoft SQL: The target table ‘dbo.TableName’ of the DML statement cannot have any enabled triggers if the statement contains an OUTPUT clause without INTO clause

And this

Inner exception: Microsoft SQL: The target table ‘dbo.TableName’ of the DML cannot have any enabled triggers if the statement contains an OUTPUT clause without INTO clause”

If i create the app from within the portal i get the error

"Network error when using patch function: An error occured on the server"

Can anyone help?

I have googled in this forum and i get two solutions to fix

1. Use a stored procedure
2. Remove any triggers associated with the SQL table

With 1. could someone explain how to do that and help me?

and With 2. is the trigger the datasync? because i cannot turn it off as i need to send data from this table to another one in another database (same server)

I am at wits end all i want to do is patch to a SQL table and datasync the same table.. Its crucial for the months of work i have put in and i cannot see a way to do this any other way.

[PaulD1]

To be able to patch to a SQL table from PowerApps, the table must have a primary key and cannot have any triggers.

You can't call a stored proc directly from PowerApps.

You can create a PowerAutomate flow that can be called from PowerApps (with parameters) and the flow can then call the SQL Stored proc (and pass values back to Power Apps - such as success/fail or the ID of the newly patched record). The downside is the extra complexity the incorporation of the flow introduces for maintenance and trouble-shooting plus, potential, some degraded performance due to the 'extra links in the chain' - though my expectation is that would not be significant. 

Watch out of licensing - every user will need a premium license to use the App (though that would also be the case even if you had no triggers and were able to patch directly to the SQL table from the App).

[SomeoneAwesome]

Hi, Thanks for the advice,

The table has a primary key,

What exactly does triggers mean in this context?

Im sort of understanding the stored procedure, but how would it help myself patch to sql?

Licensing is a PowerApp Per User Plan.

[PaulD1]

In SQL Server, a trigger is a piece of code that can fire each time a record is added/modified/inserted. A common use is to populate some form of audit table.

I don't know why PowerApps is unable to patch to a SQL Server table with a trigger - Access has been able to do so for decades. 

The Stored Proc will be able to insert/update/delete records no problem, but to call the Stored Proc you will need to use a Flow as an intermediary step. PowerApps (cannot call a stored proc) -> Flow (can call a stored proc) -> SQL Server.

You might be able to just perform your insert/update directly from the Flow (no need for Stored Proc) - I can't recall now if Flow is also has limitations relating to triggers.

[SomeoneAwesome]

Right, so i know i dont have any triggers, however quite a few views are created from the datatable in question.

I tried using the flow action before "update a row" into the SQL table as my own work around, i figured create a temp table and use flow to update said problem table with datasync when a new item is added into the temp table. Im beginning to think Azure SQL isnt the way to go and ill have to look into another option despite spending 10's of 1000s in the last few months to get to the final stage and i cant patch to SQL because of a datasync issue has really depressed me, also it has made me look incompetent because no where does it say this isnt possible. Im frustrated at this, not you. Thankyou for your help.

[PaulD1]

I'm not sure what is meant by 'datasync' here - can you provide any screen shots? As far as I know, as long as the table has a primary key and does not have any triggers, PowerApps should be able to patch to it. 

Do you have any replication set up (I have never tried updating a table that is part of replication)?

Dumb questions - but can you insert directly into the table via a SQL Statement and/or SSMS? Are you sure you are updating a table and not a view (PowerApps can't patch to a SQL View).

[SomeoneAwesome]

Datasync is used in the Azure Portal to send data from one database to another on the same server.

Yeah i set up a trial of exact same tables one without Datasync on and one with it off and it works perfect with the one turned off. I put in a Microsoft Support Ticket. But am patiently awaiting response from their suggestions which didnt work (i get the feeling ive found another bug)

Yeah deffo table not a view.

When you mean insert directly into the table? do you mean just running a query? because i dont want to do that as that would mean a manual upload also because inside the app i performed a heap of data integrity checks which stop duplicate data and the users using key ID's twice, plus a whole heap of verification background things.

[PaulD1]

I'm not familiar with Datasync but it sounds like that is the problem then.

WRT doing a 'manual insert', I meant just to test that you could manually insert records in case the issue is on the SQL side.

PowerApps support for SQL is very poor IMO - e.g. no support for updating views, no support for create/update/delete on tables with triggers, no support for calling Stored Procs. The fault for this lack of support lies on the PowerApps side as plenty of other products/services do not have these issues (as mentioned MS Access has no such restrictions going back decades).

I suspect that, because PowerApps sits within the MS 'Business Systems' division rather than the 'Data' division, the Product Managers' bonuses are much more positively affected by sales of Dataverse than of SQL, so making PowerApps work better with SQL is low on their priority list. </cynic>

[SomeoneAwesome]

Yeah it makes sense.

Well i kind of cant turn the datasync off as AFAIK there is no other way to send data from one AZURE SQL database to another on the same server.