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

Use SQL Store Procedure in Power BI

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.

39 REPLIES 39
asocorro Member
Member

Re: Use SQL Store Procedure in Power BI

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.

Connect with me in LinkedIn: https://pr.linkedin.com/in/adolfosocorro
Follow me on Twitter: https://twitter.com/AdolfoSocorro
Highlighted
nirajdubey Frequent Visitor
Frequent Visitor

Re: Use SQL Store Procedure in Power BI

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''');

stangellapally Frequent Visitor
Frequent Visitor

Re: Use SQL Store Procedure in Power BI

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

Tango

Re: Use SQL Store Procedure in Power BI

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


Near SE WI? Join our PUG Milwaukee Brew City PUG

Re: Use SQL Store Procedure in Power BI

@stangellapally@eferreira@nirajdubey@asocorro 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)


Near SE WI? Join our PUG Milwaukee Brew City PUG
stangellapally Frequent Visitor
Frequent Visitor

Re: Use SQL Store Procedure in Power BI

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

 

Thanks much @Seth_C_Bauer!

Tango
Microsoft ZhiqiangZhou
Microsoft

Re: Use SQL Store Procedure in Power BI

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

Baskarved Frequent Visitor
Frequent Visitor

Re: Use SQL Store Procedure in Power BI

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.

Baskarved Frequent Visitor
Frequent Visitor

Re: Use SQL Store Procedure in Power BI

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.

Helpful resources

Announcements
New Kudos Received Badges Coming

New Kudos Received Badges Coming

Kudos to you if you earned one of these! Check your inbox for a notification.

Microsoft Implementation for Communities Wins Award

Microsoft Implementation for Communities Wins Award

Learn about the award-winning innovation that was implemented across Microsoft’s Business Applications Communities.

Power Platform World Tour

Power Platform World Tour

Find out where you can attend!

Top Kudoed Authors (Last 30 Days)
Users online (1,629)