cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
EJRebirth
Level: Powered On

On-Prem SQL Stored Procedure only returning as action

Hello,

 

I've posted this on another community forum in error (the Power Apps community) before realising my issue was with Microsoft Flow.

 

We're currently in the process of testing out Microsoft Flows to use within PowerApps, and we've managed to connect to our On-Premise SQL 2014 server succesfuly.

 

We're trying to run a variety of on-premise Scalar Functions and we are met with instant failure with a typical error message being:

 

We cannot convert a value of type Table to type Action. inner exception: We cannot convert a value of type Table to type Action

 

or

 

We cannot convert the value true to type Action.

 

Is it even possible to have data returned, or is this a limitation of using an on-premise gateway currently? I simply do not see a way to have data returned properly. Can anyone advise?

Capture.PNG

I have attached a picture for completeness. This function ascertains if a date falls on a working day which should return true or false. As you can see, it returns True, but the flow wants to return as an Action.

 

Is there anyone that can advise as to what is going wrong, every function is trying to convert to an action, when I just want it to return as the datatype it's intended for.

6 REPLIES 6
Community Support Team
Community Support Team

Re: On-Prem SQL Stored Procedure only returning as action

Hi @EJRebirth,

 

Please make sure your stored procedure is properly configured.

 

I made a simple flow for testing with the action Execute stored procedure, and the value can be returned properly. Please check the following screenshot.

1.PNG

 

Known issues and limitations about SQL Server, please check it at here:

https://docs.microsoft.com/en-us/connectors/sql/

  

 

Best regards,

Mabel Mao

Community Support Team _ Mabel Mao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
EJRebirth
Level: Powered On

Re: On-Prem SQL Stored Procedure only returning as action

Hi Mabel Mao,

 

I've attempted to create a fresh function which only selects a value from a table, and the error is still the same. Could you provide me with your working example to compare differences.

 

 

Highlighted
Community Support Team
Community Support Team

Re: On-Prem SQL Stored Procedure only returning as action

Hi @EJRebirth,

 

Please take this blog for a reference:

How To Create A Stored Procedure In SQL Server Management Studio

 

The stored procedure I made on my side is rather simple, please check the following:

1.PNG

 

Hope this could be a reference for you.

 

 

Best regards,

Mabel Mao

Community Support Team _ Mabel Mao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
EJRebirth
Level: Powered On

Re: On-Prem SQL Stored Procedure only returning as action

Ok, so I've discovered that Store Procedures work fine regardless, but Scalar-valued Functions do not, despite being selectable from the list. Maybe this is where the confusion has come in.

 

I can run a stored procedure, that calls a scalar-valued function, and this works, but running a scalar function direct always fails with the same message regarding unable to convert type to action.

 

Are Scalar-Valued Functions still a work in progress, or in preview, or do they simply not work despite being avaiable to select?

 

I have included the test for you to try yourself.

 

CREATE FUNCTION [dbo].[testScalar]
(
	-- Add the parameters for the function here
	@a int = 1,
	@b int = 2
)
RETURNS int
AS
BEGIN
	-- Declare the return variable here
	DECLARE @c int

	-- Add the T-SQL statements to compute the return value here
	SELECT @c = (@a+@b)

	-- Return the result of the function
	RETURN @c

END
GO
EJRebirth
Level: Powered On

Re: On-Prem SQL Stored Procedure only returning as action

Was wondering if you've had a chance to try my test out, and whether there is indeed an issue with what I'm doing, or if there is an issue with Flow in general?

Steveo1
Level: Powered On

Re: On-Prem SQL Stored Procedure only returning as action


@EJRebirth wrote:

Ok, so I've discovered that Store Procedures work fine regardless, but Scalar-valued Functions do not, despite being selectable from the list. Maybe this is where the confusion has come in.

 

I can run a stored procedure, that calls a scalar-valued function, and this works, but running a scalar function direct always fails with the same message regarding unable to convert type to action.


@EJRebirth, We have the same issue with a table value function.  I can see it in the list of stored procs, but cannot run it without the same error you got.  Did you get this to work without calling a stored proc that calls a function?

Helpful resources

Announcements
thirdimage

Power Automate Community User Group Member Badge

Fill out a quick form to claim your user group badge now!

firstImage

Incoming: New and improved badges!

We've given our badges an overhaul and also added some brand new ones!

fifthimage

Microsoft Learn

Learn how to build the business apps that you need.

sixthImage

Power Platform World Tour

Find out where you can attend!

seventhimage

Webinars & Video Gallery

Watch & learn from the Power Automate Community Video Gallery!

Top Kudoed Authors (Last 30 Days)
Users online (4,743)