SQL Server Execute Stored Procedure V2 error

[Dom32]

Hello, 

I am just getting started using Power Automate and Power Apps (so real NEWBIE) and I am trying to use the SQL Server Execute Stored Procedure (V2) using a very simple SP (insert a new row in a table). After I select the server, database and stored procedure I get an error message : 'body" is required (see image below). What am I doing wrong?

I know my on premise gateway connection is working because I tested it with a SQL Server Insert row and that was working.

Any help/guidance would be much appreviated.

Thanks!

[fchopo]

Hello @Dom32 

Which type of authentication do you use to connect to SQL Server? Maybe it's something with the connection. Could you provide more detailed information?

Ferran

[Dom32]

Hi Ferran

the connection is using SQL authentication. I was able to get the Execute Stored Procedure V2 working using a Power Automate button. 

I just find it strange that it is not working with a Power Apps (even though they have a template for it). I would like to be able to trigger the SP within a Power App form.

thanks!

[fchopo]

Hello @Dom32 

Really weird. Have you tested it with another SP just in case there's something with it? There's an excellent video on how to call SP from PowerApps here: https://powerusers.microsoft.com/t5/Webinars-and-Video-Gallery/Calling-Stored-Procedures-from-within...

Hope it helps!

Ferran

[Dom32]

Hello group

well, I added a variable as input in my stored procedure to update one of the field and everything started working as expected. I guess I should have pay more attention to the "requirements" 🙂

Initially I was trying to execute the SP without a variable.

Sorry for your time, love this Power Automate stuff.

D

[dvtkiet]

I have the same problem, and my stored procedure doesn't need any input parameter (it creates a table from different tables.
The stored procedure executes well in SQL; but when I call the stored procedure using Execute Stored Procedure V2, I get the error "Body is required."
Thank you in advance for your help.

[Dom32 · Accepted solution]

Hi dvtkiet

I think the stored procedured requires at least one "INPUT" per the online documentation. I added an input to my stored procedured and I was able to use it with a button no problem.

https://docs.microsoft.com/en-us/connectors/sql/#execute-stored-procedure-(v2)

Hope it works for you.

Cheers

[dvtkiet]

Hi @Dom32, It works. Thank you so much for your help and the documentation.

[Zap174]

Hi @dvtkiet 

How did you add an input to your stored procedure? Could I see an example?

[Dom32]

This is how I added my INPUT to my stored procedures

then in Powerautomate, the INPUT will be listed

[Zap174]

Thank you so much!

[DavidInBkk]

Personally, rather than using resultsets, I would use JSON instead. Someting like this:

CREATE PROC [logdata].[UpdateShopifyOrdersDataV2]
(
 @ID BIGINT,
 @JSON NVARCHAR(MAX) OUTPUT
)
AS
BEGIN
SET @JSON =
   (
   SELECT TOP 1
       Id
       ,StoreCode
       ,OrderName
       .
       .
       .
   FROM
        LogData.GetInvoiceData I
    WHERE
         Id = @ID
     FOR JSON PATH
     );

END;

Then put the JSON through the JSON object power automate, use a sample output of your json query to create the schema, and go from there. Much easier to deal with and works great in Azure or on-premise.

[chent7]

This issue happened to me when i deployed my solution from dev to staging.

My flow, which doesn't take any input from powerapps, executes a GET stored procedure (V2). It worked fine in my dev environment but got this error message in staging environment. 

I simply removed then recreated the PowerApps(V2) step and Execute Stored Procedure(V2) steps in flow, save, and everything works fine. Error message is now gone. 

[n0va]

Parameter List is a JSON field, so using {} (an empty JSON object) works.
Found in a SQL tech community  post and it worked for me.