SQL Server Error 502 BadGateway when using PATCH and REMOVE

[madpatty]

Hi, 

I have been having issues with Inserting and deleting rows from my SQL Server table.

Here is the error code I received when I try to insert a row into my table using PATCH():

ForAll(rowData2,Patch('[dbo].[TableName]',Defaults('[dbo],[TableName]'),{ groupId:groupId, memberId:memberId,dateAcknowledged:dateAcknowledged}))

Error Response:

    "error": {
      "code": 502,
      "source": "msmanaged-na.azure-apim.net",
      "clientRequestId": "c4fc28a2-fd97-46a0-8ca6-c2313a14d407",
      "message": "BadGateway",
      "innerError": {
        "status": 502,
        "message": "Microsoft SQL: Parse error at line: 2, column: 1: Incorrect syntax near 'output'.\r\nclientRequestId: c4fc28a2-fd97-46a0-8ca6-c2313a14d407",
        "error": {
          "message": "Microsoft SQL: Parse error at line: 2, column: 1: Incorrect syntax near 'output'."
        },

Table details:

CREATE TABLE dbo.TableName
(    
 groupId varchar(100) NOT NULL ,   
 memberId varchar(100) NOT NULL,  
 dateAcknowledged varchar(100) NOT NULL  
 );
 ALTER TABLE dbo.TableName add CONSTRAINT CompositeKey_1 PRIMARY KEY NONCLUSTERED (groupId,memberId,dateAcknowledged) NOT ENFORCED;

My SQL connection is Azure AD Integrated.

I am getting a similar error when I try to delete rows using the REMOVE() function. I tried using "Insert Row(V2)" Flow, still getting the same error. 

I hope I provided enough information about my error message. Been stuck at this for about 3 days.

Thank you

[v-bofeng-msft · Accepted solution]

Hi @madpatty :

I've made a same test but did not encountered the problem you mentioned:

1\Create the table in my data base:

CREATE TABLE dbo.TableName
(    
 groupId varchar(100) NOT NULL ,   
 memberId varchar(100) NOT NULL,  
 dateAcknowledged varchar(100) NOT NULL  
 );
 ALTER TABLE dbo.TableName add CONSTRAINT CompositeKey_1 PRIMARY KEY NONCLUSTERED (groupId,memberId,dateAcknowledged);

Because "NOT ENFORCED" reported an error, so I deleted it.

2\Connect to [dbo].[TableName] in my canvas app

3\Create a custom collection-rowData2

ClearCollect(rowData2,{groupId:"111",memberId:"2222",dateAcknowledged:"dsf"})

4\Add a button and set it's OnSelelct proeprty to:

ForAll(
  rowData2,
  Patch(
    '[dbo].[TableName]',
    Defaults('[dbo].[TableName]'),
    { groupId:groupId, memberId:memberId,dateAcknowledged:dateAcknowledged}
  )
)

The Result:

I suggest you re-create the connection. If the problem is still not resolved, I suggest you create a support ticket.

Best Regards,

Bof

[TheFlowHawk]

@madpatty did you ever discover the solution? I'm facing the same error message.

[madpatty]

I believe the problem was either using 'NOT ENFORCED' when creating the table or because I was using an old SQL version w/ Datawarehouse.

I ended up switching to Azure SQL db. Used the following to create my table

CREATE TABLE dbo.groupAudit
(
  groupId VARCHAR(100) NOT NULL,
  ownerId VARCHAR(100) NOT NULL,
  campaignId VARCHAR(100) NOT NULL,
  groupCheck bit,
  membersCheck bit,
  response VARCHAR(100), 
  CONSTRAINT PK_groupAudit PRIMARY KEY (groupId,ownerId,campaignId)
)

As for PowerApps, I created an Edit Form named AuditFrom. Which loads in all the fields in the table into the powerApp. 

Datasource: [dbo].[groupAudit]

and a submit button with OnSelect: SubmitForm(AuditForm).

[SamElmer]

I also had this issue where the table had a trigger. Removing the trigger fixed the error.