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

Flow executing Stored procedure on premises SQL

Setup:

On premisis SQL server, user have SYSADMIN role

Simple stored procedure

 

INSERT INTO [dbo].[EmployeeSteps]
([EmployeeId],[StepsId],[Completed],[CompletedDate],[CompletedBy],[Steporder])SELECT 11,1,0,NULL,NULL,1

 

No parameters, SP executes fine in Management studio.

 

Error in Flow: 

{
"status": 400,
"message": "The key didn't match any rows in the table.\r\n inner exception: The key didn't match any rows in the table.\r\nclientRequestId: eef49f79-4607-472f-8661-d9d037948816",
"source": "sql-we.azconn-we.p.azurewebsites.net"
}
 
No triggers, Id is Identity. 
 
Can't get passed this error. 
 
error.png
9 REPLIES 9
Super User III
Super User III

Hi @ClaesYlving ,

 

Check your sql connection for that flow step. You might have more than one connection and pointing the wrong connection here.

 

If this reply has answered your question or solved your issue, please mark this question as answered. Answered questions helps users in the future who may have the same issue or question quickly find a resolution via search. If you liked my response, please consider giving it a thumbs up.

 

Thanks



Did I answer your question? Mark my post as a solution!

If you liked my response, please consider giving it a thumbs up


Proud to be a Flownaut!

Learn more from my blog

Power Automate Video Tutorials
Community Support
Community Support

Hi @ClaesYlving ,

 

Please make sure that the columns have the exact same names, order and number of them as the original file.

 

When testing the action Execute stored procedure on my side, it is working properly.

1.PNG

 

Best regards,

Mabel    

      

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.

I have checked my connection, even created a new one to be certain. No luck.

The columns are not defined in flow, they are in the Stored Procedure, and that executes fine from Management studio. 

I can see with profiler that the flow executes querys from metadata and receieves the correct SP name, but then any execution stops. The SP itself is never called.

 

Any more informaiton that I can provide, i will.

 

Regards

Claes

Hi @ClaesYlving ,

 

Do you have multiple environments? If it is please make sure you are using the right environment for flow & SQL connector.

 

If this reply has answered your question or solved your issue, please mark this question as answered. Answered questions helps users in the future who may have the same issue or question quickly find a resolution via search. If you liked my response, please consider giving it a thumbs up.

 

Thanks



Did I answer your question? Mark my post as a solution!

If you liked my response, please consider giving it a thumbs up


Proud to be a Flownaut!

Learn more from my blog

Power Automate Video Tutorials


@abm wrote:

Hi @ClaesYlving ,

 

Do you have multiple environments? If it is please make sure you are using the right environment for flow & SQL connector.

 

If this reply has answered your question or solved your issue, please mark this question as answered. Answered questions helps users in the future who may have the same issue or question quickly find a resolution via search. If you liked my response, please consider giving it a thumbs up.

 

Thanks


Yes, I am running under Default environment.

New Member

Some more informaiton;

 

When executing flow in the browser, I get this activity on the SQL server (I will omitt the SQL-querys):

 

Firstrly the SQL server version

Result 2017

 

Then all the tables 

TABLE_CATALOG TABLE_SCHEMA TABLE_NAME TABLE_TYPE CREATED_DATE MODIFIED_DATE DESCRIPTION
Corporate dbo EmployeeSteps BASE TABLE 2019-07-03 13:08:41.853 2019-07-03 13:09:05.993 NULL
Corporate dbo Steps BASE TABLE 2019-07-03 12:57:06.073 2019-07-03 12:57:06.217 NULL
Corporate dbo Employee BASE TABLE 2019-07-03 09:43:47.037 2019-07-03 13:53:29.043 NULL

 

Lastly all the SP's

ROUTINE_SCHEMA ROUTINE_NAME ROUTINE_TYPE CREATED_DATE MODIFIED_DATE DESCRIPTION
dbo AddStepsForEmployee PROCEDURE 2019-07-03 13:58:42.007 2019-07-03 14:36:19.730 NULL
dbo AddStepsForEmployee1 PROCEDURE 2019-07-03 14:37:54.040 2019-07-03 14:37:54.040 NULL

 

After this step, I get a bunch of queries for potential parameters, and since my SP has no parameters they all result in no rows. 

 

After this ALL activity on SQL-Server side stops.

 

I did another test:

 

New table

CREATE TABLE [dbo].[tstTbl](
[id] [int] IDENTITY(1,1) NOT NULL,
[txt] [varchar](50) NULL,
CONSTRAINT [PK_tstTbl] PRIMARY KEY CLUSTERED
(
[id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

 

New Procedure

CREATE PROCEDURE [dbo].[Sp_Test]
AS
BEGIN
INSERT INTO [dbo].[tstTbl]
([txt])
VALUES
('A')

END

 

Calling this from the flow does not even call the SP. Almost feels like the flow does not get the correct respons from the master table asking about the Tables, SP's and Prameters, I am lost.

Hi @ClaesYlving 

 

When you run the Flow, verify that the correct SQL connection has been specified. This has caught me various times, as the error sounds like a data related error. Meantime, it can't find the object in SQL. Which also explains why is doesn't look like Flow is calling the SP....it isn't, it probably can't find it 😉

 

Verify Connections in Flow Run.png

 

FYI: Below are two videos dealing with advanced queries to SQL from PowerApps (using Flow).
For SQL Azure you can use direct (native) queries:
https://youtu.be/DII10gK715I

 

For on-premesis SQL servers, native queries are not supported from Flow (you will get an error: operation Execute Native Sql is currently not supported using an on-prem gateway connection), so we have to use stored procedures to accomplish the same:
https://youtu.be/BAGBzI4zdww

 

Please let me know if you don't come right.

 

Dawid van Heerden
Follow on Twitter: @davestechtips
Subscribe to YouTube: https://www.youtube.com/davestechtips?sub_confirmation=1
**If you found this reply helpful, please mark this as the answer to close the topic and make it easier to find for other people with similar questions.

@DavesTechTips this exactly fixed my errors. I was looking into for almost 1 hour before reading your post!

Thanks!

Helpful resources

Announcements
PP Bootcamp Carousel

Global Power Platform Bootcamp

Dive into the Power Platform stack with hands-on sessions and labs, virtually delivered to you by experts and community leaders.

secondImage

Power Platform Community Conference On Demand

Watch Nick Doelman's session from the 2020 Power Platform Community Conference on demand!

MPA Community Blog

Power Automate Community Blog

Check out the community blog page where you can find valuable learning material from community and product team members!

Top Solution Authors
Users online (6,774)