topic Re: Use SQL Store Procedure in Power BI in Desktop

Use SQL Store Procedure in Power BI

eferreira — Tue, 23 Feb 2016 18:16:28 GMT

I have created a basic stored procedure without parameters in SQL Server, when I use the store procedure in Power Bi Desktop the return is ok in design but when I click on Save and Close in datasource edit the Power Bi gives me an errormessage like below, I'm trying to use this procedure as Direct Query.

Microsoft SQL: Incorrect syntax near the keyword 'EXECUTE'. Incorrect syntax near ')'.

I tried to change the statement but I got nothing.

The sql statement to execute the procedure is:

EXECUTE [Person].[SelectpersonByType]

I'm using the AdventureWorks database sample from Microsoft and my procedure statement is:

CREATE PROCEDURE [Person].[SelectpersonByType]
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

-- Insert statements for procedure here
select * from Person.Person
END

My customer is testing Power BI and are facing the same error, thanks in advanced.

Re: Use SQL Store Procedure in Power BI

asocorro — Tue, 23 Feb 2016 18:27:45 GMT

You must be using DirectQuery mode, in which you cannot connect to data with stored procedures. Try again using Import mode or just use a SELECT statement directly.

Re: Use SQL Store Procedure in Power BI

nirajdubey — Thu, 02 Jun 2016 13:20:32 GMT

Its a late reply, but it may help some body else, you can use openquery. Follwoing syntax will work with both import data and direct query

SELECT *
FROM OPENQUERY ([server name],
'EXEC dbname.dbo.spname @parametername = ''R1''');

Re: Use SQL Store Procedure in Power BI

stangellapally — Thu, 23 Jun 2016 15:00:08 GMT

I am having similar issue and tried both the solutions posted:

1. tried to change from Direct query to import but the Power BI doesnt give me that option to change.

2. Also tried the OPENQUERY OPTION and I am now getting the below message:

Details: "Microsoft SQL: Server 'sv375002\lt1201' is not configured for DATA ACCESS."

Re: Use SQL Store Procedure in Power BI

Anonymous — Thu, 23 Jun 2016 16:10:07 GMT

stangellapally

1. tried to change from Direct query to import but the Power BI doesnt give me that option to change.

It is most likely because you already have a connection to a SQL source in Direct Query mode. If that is the case, I believe this gets greyed out.

2. Also tried the OPENQUERY OPTION and I am now getting the below message:

Details: "Microsoft SQL: Server 'sv375002\lt1201' is not configured for DATA ACCESS."

You would need to enable direct access on your SQL Server. You can check what it is currently set at by running this:

select server_id, name, is_data_access_enabled from sys.servers

But I would definitely check with your DB team (if you aren't) before switching anything.

To enable you would execute EXEC sp_serveroption '<Your ServerName>', 'DATA ACCESS', TRUE

After enabling that feature you will be able to successfully execute the OpenQuery.

As noted, be sure you understand what you are enabling and what it is doing before executing anything.

Re: Use SQL Store Procedure in Power BI

Anonymous — Thu, 23 Jun 2016 16:37:50 GMT

stangellapallyeferreiranirajdubeyasocorro As a follow up to my previous post, i was testing this further due to another thread, and have discovered that you don't need to change any database settings or use OpenQuery. If you wrap your stored procedure in a variable you can import the data using a sproc (Doesn't work in Direct Query, only import)

example:

DECLARE @sqlCommand varchar(1000)

SET @sqlCommand = 'dbo.Testproc'
EXEC (@sqlCommand)

Re: Use SQL Store Procedure in Power BI

stangellapally — Thu, 23 Jun 2016 19:18:44 GMT

This solution seems to be working and its lot easier too.

Thanks much Anonymous!

Re: Use SQL Store Procedure in Power BI

ZhiqiangZhou — Fri, 15 Jul 2016 05:31:19 GMT

nirajdubey Thank you. I succeeded in using the SP by OpenQuery as you introduced.

Re: Use SQL Store Procedure in Power BI

Baskarved — Tue, 19 Jul 2016 14:12:35 GMT

Really It is awesome.It could help multiple users.As most of them not aware of this solution.If we might initimate in forum it would be great.

Re: Use SQL Store Procedure in Power BI

Baskarved — Tue, 19 Jul 2016 14:18:47 GMT

Small query over here,If we need to pass parameters to the procedure then how can we use this here?Could you share the syntax so that it would be helpful for beginners.

Thanks.

Re: Use SQL Store Procedure in Power BI

Vivs18 — Wed, 20 Jul 2016 14:37:49 GMT

Anonymous I tried proposed solution but I am getting below error in Direct Query mode against Azure SQL data source.

Details: "EvaluateNativeQueryUnpermitted failure: the query 'DECLARE @sqlCommand varchar(1000)
SET @sqlCommand = 'dbo.uspGetSyncEventSchedulePB'
EXEC (@sqlCommand)
' isn't approved for execution."

I am not sure if I am missing something as datasource is Azure Sql.

Re: Use SQL Store Procedure in Power BI

Anonymous — Wed, 20 Jul 2016 17:15:03 GMT

Vivs18 If you note, I make reference in my solution that it only works with Import. Not when using Direct Query.

If you want to continue to use DQ, could you create a View and reference that?

Re: Use SQL Store Procedure in Power BI

Vivs18 — Thu, 21 Jul 2016 15:11:23 GMT

Anonymous I missed import in provided solution. But I tried with the proposed solution with Import but still it resulted in same error. I am not sure if I am missing anything here.

Re: Use SQL Store Procedure in Power BI

Anonymous — Thu, 21 Jul 2016 16:40:22 GMT

Vivs18 It could be a limitation in Azure SQL DB... There are a bunch of differences - Reference

Re: Use SQL Store Procedure in Power BI

radz — Fri, 22 Jul 2016 11:19:36 GMT

Anonymous Hi, thanks a lot for your solution. It works!

But a query which follows up this is to "Pass parameters within a stored procedure(In SQL say) so that the parameter values can be called/used in parameters made in Power BI(query) ?"

For eg: I have a SP in SQL which has 2 parameters (para 1 and para2) which can take multiple/single value(s).

I call this SP in Power BI Query Editor( just like you mentioned) and I can now see it in my data sets.

What I want is, to create a parameter(say para) in Power BI which has its source as the SP in SQL such that if I pass a value into para(in Power BI), then data loaded from the SQL Server(my data source) into Power BI corresponds only to the value(s) I added.

Is this possible?

Thanks in advance!

[This may help in getting required data loaded only into Power BI, saving time.]

Re: Use SQL Store Procedure in Power BI

Anonymous — Fri, 22 Jul 2016 15:29:23 GMT

radz I haven't figured out a way to do this yet, but I would think it would be possible

Re: Use SQL Store Procedure in Power BI

dsilveira — Thu, 01 Sep 2016 20:26:44 GMT

@radz

Hi, I had the same problem and found a way to use the parameters

DECLARE @sqlCommand varchar(1000)
SET @sqlCommand = 'sp_name'
EXEC @sqlCommand @Parameter1= 39, @Parameter2= 'myString', @Parameter3 = 'myString2'

I hope it helps somebody.

Regards.

Re: Use SQL Store Procedure in Power BI

Anonymous — Thu, 01 Sep 2016 21:00:30 GMT

dsilveira Nice! Thanks for sharing.

Re: Use SQL Store Procedure in Power BI

premjitsaha — Tue, 03 Jan 2017 14:41:41 GMT

How to use it if SQl stored procedure has a parameter?

Re: Use SQL Store Procedure in Power BI

Anonymous — Tue, 03 Jan 2017 15:06:32 GMT

premjitsaha Use dsilveira's solution above... Is there a different scenario you have in which it isn't working?