cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
rutgerverhaar
Regular Visitor

Power Automate Bad Gateway error while trying to insert a row from lists to sql server

I have been trying to create a very simple (lists to sql) power automate workflow that triggers when an item gets added/modified in Microsoft Lists and gets updated in my SQL DWH.

The Title column is the default single-line text field provided by Microsoft. The Comments column is a custom field with data type multiple-lines of text.

 

 

In SQL I created a straightforward table, which contains two columns 'Title' and 'Comments', both of type varchar.

The Flow I created looks like this: 

flow.jpg

 

 

During the first sharepoint action I connect to the sharepoint list, no advanced options. During the get rows(v2) action I connect to the created table in SQL, again no advanced options. In the condition step I included some basic logic that determines whether the item in the list is new or exists. In the final step, that inserts or updates the row based on its existence, I run into an error. While all the previous steps work perfectly fine, I run into a 502 BadGateway error saying that the Insert_Row action has failed. I have been stuck for the last couple of days and haven't been able to find the solution. My SQL DWH is located in Europe and the PowerApps environment is set to EMEA.

 

Can someone please help out?

 

Please find below the error report: 

 

rutgerverhaar_7-1618832869988.png

 

rutgerverhaar_8-1618832927942.png

 

 

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
LaelH
Advocate III
Advocate III

Hi rutgerverhaar

 

I can't help you with your problem, but I can  propose an alternative means of achieving the same thing which may be a whole lot more efficient. Rather use the Execute a SQL Query and then use a "merge" query.  

Flow would look like this

LaelH_0-1618839030127.png

And sql query would be something like:

Merge into MyTable using

(...my data fetched from sharepoint...) spData

on (spData.Title = MyTable.Title)

WHEN MATCHED THEN
UPDATE SET 

MyTable.Title= spData.Title,
MyTable.Comments = spData.Comments

WHEN NOT MATCHED THEN
INSERT ( MyTable.Title, MyTable.Comments)
VALUES (spData.Title, spData.Comments)

 

Hope that helps

 

View solution in original post

4 REPLIES 4
LaelH
Advocate III
Advocate III

Hi rutgerverhaar

 

I can't help you with your problem, but I can  propose an alternative means of achieving the same thing which may be a whole lot more efficient. Rather use the Execute a SQL Query and then use a "merge" query.  

Flow would look like this

LaelH_0-1618839030127.png

And sql query would be something like:

Merge into MyTable using

(...my data fetched from sharepoint...) spData

on (spData.Title = MyTable.Title)

WHEN MATCHED THEN
UPDATE SET 

MyTable.Title= spData.Title,
MyTable.Comments = spData.Comments

WHEN NOT MATCHED THEN
INSERT ( MyTable.Title, MyTable.Comments)
VALUES (spData.Title, spData.Comments)

 

Hope that helps

 

kierian
Community Support
Community Support

Hello 

Can you please provide flow URL and run  URL

Have a good day

 

Thank you for the alternative! As soon as I found the issue that's causing the error when trying to update or insert rows into SQL i'll look into your proposed workaround!

I had exactly this 502 and found it to be a data error in one of my fields (using a True/False into a Yes/No field). Once I realised that by properly looking at the Raw Output from the sql action, I fixed it and all ok now.

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.

New Ideas Forum MPA.jpg

A new place to submit your Ideas for Power Automate

Announcing a new way to share your feedback with the Power Automate Team.

MPA Virtual Workshop Carousel 768x460.png

Register for a Free Workshop

Learn to digitize and optimize business processes and connect all your applications to share data in real time.

MPA Licensing.jpg

Ask your licensing questions at the Power Automate AMA!

Join Priya Kodukula and the licensing team, super users and MVPs to find answers to your questions on Power Automate licensing.

Users online (4,949)