You’re offline. This is a read only version of the page.
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:
@DavesTechTips this exactly fixed my errors. I was looking into for almost 1 hour before reading your post!
Thanks!
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.
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.
@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.
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
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
I have checked my connection, even created a new one to be certain. No luck.
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
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
#1
WarrenBelz
146,651
Most Valuable Professional
#2
RandyHayes
76,287
Super User 2024 Season 1
#3
Pstork1
65,997
Most Valuable Professional
Share
Report
All responses (
Answers (
