Flow executing Stored procedure on premises SQL

[ClaesYlving]

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. 

[abm]

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

[v-yamao-msft]

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.

Best regards,

Mabel    

[ClaesYlving]

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

[ClaesYlving]

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

[abm]

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

[ClaesYlving]

---

@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.

[ClaesYlving]

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.

[DavesTechTips]

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 😉

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.

[db1989]

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

Thanks!