Answered

Unable to Execute Stored procedure

(0) Share

Report

Posted on by PeterBurton

Hello All,

I am attempting to generate some data for my Flow by executing a stored procedure. I have created a gateway on a Windows server running SQL 2012, which allows a connection to a database instance.

I am able to return table names and rows for this database without any issue. When I select the option to execute the stored procedure I am unable to generate a list of procedures and am presented with an "Unknown Error". Manually specififying the procedure name does not work.

Screenshot: http://imgur.com/a/jwxds

I am able to log into SSMS using the credentials supplied for the gateway / connection and execute the procedure without issue. However I "feel" like there is a permission issue that is preventing the flow engine from reading the object / container that is holding the stored procedures, but I don't know where to look.

Any help or direction with this would be much appreciated!

Regards,

Peter Burton

Categories:

Using connectors

All responses (7)

Answers (1)

Sort by

Verified answer

Sunay Microsoft Employee on at

Like
a
(0)

Report
Copy link

Link copied!

Hi @PeterBurton,
Stored procedures are unfortunately not supported while using a Gateway. We are working on supporting this functionality and will keep you updated as soon as its ready.

Thanks,
Sunay
PeterBurton 8 on at

Like
a
(0)

Report
Copy link

Link copied!

Hey Sunay,

Thanks for confirming. Is there any indication of the time frame around when a feature of this nature is expected to be implemented or is this unknown at this stage?

Also you mentioned it is unsupported using a gateway. I am a little confused by this, as I couldn't see how you would be able to direct Flow to a database with a stored proc, other than using a gateway? Is there a way to self host Flow or equivalent to bypass this requirement?

Regards,

Peter
jaj 8 on at

Like
a
(1)

Report
Copy link

Link copied!

If stored procedures are not supported, which SQL operations are supported? Is "add a row" supported?
newsomaj 5 on at

Like
a
(0)

Report
Copy link

Link copied!

Yes, I am unable to get the sql connections using a gateway working at all 😞
jaj 8 on at

Like
a
(0)

Report
Copy link

Link copied!

Hi Newdomaj,

I managed to make insert row work. The wizard lists the columns available to from the table.
Consider:
1) Ensure the Account you use has reader/writer access to the database and table
2) Ensure you create a connection in flow. You cannot share a connection between powerbi and flow, even though the gateway is shared (thanks DMac)
Sunay Microsoft Employee on at

Like
a
(1)

Report
Copy link

Link copied!

Hey @PeterBurton You can use stored procs for SQL databases that are exposed to Flow via a web endpoint, or if your database is hosted in SQL Azure. Stored procedure support while using a Gateway (https://flow.microsoft.com/en-us/blog/on-premises-data-gateway/) is scheduled to be released by end of November.

Thanks,
Sunay
newsomaj 5 on at

Like
a
(0)

Report
Copy link

Link copied!

Yeah I got it, what threw me off was under add a connection, the sql server authentication type was a free-form field, not the dropdown like i was used to. I typed "Windows" in it, and then everything started to work. Also the username field acted as a password field (showing ******) and so it threw me for a loop.