HELP PLEASE!!!! Execute Stored Proc V2 stopped returning result set to flow

[LaelLeeH]

Hi All,

I have a flow that calls a SQL Stored Procedure in Azure SQL which returns a result set to Flow and is then processed further inside the flow.  I had to make a change to the Stored Procedure. It still produces the exact same result set as it always has, but for some reason the result set is no longer being returned to flow. So the balance of my flow fails  

If I copy the parameters from the failed Flow into the Stored Procedure inside SSMS and run it, it works perfectly .  It produces two result sets as expected. But the Execute Stored Procedure V2 action in Flow returns two empty result sets.

I have deleted and recreated the flow. I have deleted and recreated the Stored Procedure. I have deleted and recreated the Execute Stored Procedure V2 step inside my flow and reconnected it to the stored procedure from SQL. Nothing has worked.

This is a CRISIS. Can anyone help please? URGENT!!!! Any ideas will be most welcome

Thanks

Regards

Lael

[wskinnermctc]

I don't know the answer, but I'm wondering if it is similar to another problem that has to do with tables and the trigger when a new item is created.

The trigger uses some kind of seed value that is based on the table and each time the flow runs, but if you change the table, the trigger doesn't know and the item count is thrown off.

https://www.itaintboring.com/flow/when-an-item-is-created-sql-trigger-in-power-automate/ 

I wonder if there is something in PA that is still being used as a reference, but it doesn't necessarily exist in SQL server the same way?

Could you recreate the stored procedure with a different name as well a change the name of anything else that could've been referenced by PA? 

I guess the alternative test would be to delete the connection within your connectors and recreate that. But then any other flows that were using that sql connector need to be reconnected.

The Known Issues lists these below. Did you change any column names or remove any columns?

https://learn.microsoft.com/en-us/connectors/sql/#general-known-issues-and-limitations 

• After SQL queries and stored procedures execute, the result set schema should contain unique non-empty column names.

• For any operation inside the stored procedure that returns some set of results, such as any inner SELECT statement, the result set schema should contain unique non-empty column names.

[LaelLeeH]

Hi @wskinnermctc 

Thanks for your response. It has given me a few more ideas to try out. Deleting the connection is my LAST resort though because I have loads of flows using it and you have to reset each one - which is a time consuming nightmare.

I have already tried deleting and recreating everything, but I used the same names, so your idea to try different names is probably worth a try.

Just FYI - the returned dataset is unique, and nothing changed from previous.

Regards

Lael

[LaelLeeH]

Just some feedback on the above in case anyone else gets bitten by the same issue...

I logged a call with Microsoft and worked with an Engineer to investigate the issue.  He was amazing by the way! The root cause is a JSON string being sent from Flow to SQL.  Flow declares the variable as a string. SQL declares the variable as an nvarchar(max).   Flow is 'sending' the string correctly i.e. '[{a ton of correctly formatted JSON}]', but SQL is receiving it as a string containing an empty table i.e. '[]'. All the JSON goes missing between the two.  We tried all sorts of things including changing the SQL declaration to nvarchar(4000) and nvarchar(1000) in case it was the (max) causing the issue, but nothing worked.

Anyway, we found a workaround to use for the interim - I have switched my flows to use the action "Execute a SQL Query" instead of "Execute Stored Procedure V2",  and I am issuing the variable required declarations and the Exec statement as SQL query, so I am back up and running.

Still waiting on feedback from the support request as to why it is happening, and what the fix might be.

Hope this helps someone.

Regards

Lael