cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
madpatty
New Member

SQL Server Error 502 BadGateway when using PATCH and REMOVE

 

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

1 ACCEPTED SOLUTION

Accepted Solutions
v-bofeng-msft
Community Support
Community Support

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:

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

Best Regards,

Bof

 

View solution in original post

4 REPLIES 4
v-bofeng-msft
Community Support
Community Support

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:

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

Best Regards,

Bof

 

TheFlowHawk
Frequent Visitor

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

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
New Member

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

Helpful resources

Announcements
Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Power Platform Call June 2022 768x460.png

Power Platform Community Call

Join us for the next call on June 15, 2022 at 8am PDT.

PA Virtual Workshop Carousel 768x460.png

Register for a Free Workshop

This training provides practical hands-on experience in creating Power Apps solutions in a full-day of instructor-led App creation workshop.

PA.JPG

New Release Planning Portal (Preview)

Check out our new release planning portal, an interactive way to plan and prepare for upcoming features in Power Platform.

Top Solution Authors
Top Kudoed Authors
Users online (1,621)