Use SQL Store Procedure in Power BI

[eferreira]

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.

[DBekker]

Openquery did not work for us as we have an SP that utilized a temp table.

but we have had some success using "with result sets" option of exec when that issue comes up.

for instance

SELECT *
FROM OPENQUERY ("snapserver", 
'EXEC specialprojects.dbo.CFE_DASHBOARD_Summary_BugTrendX
WITH RESULT SETS
(
( [rel] nvarchar(16) NOT NULL,
  [value] int NOT NULL,
  [year] int NOT NULL,
  [series] nvarchar(16) NOT NULL,
  [sortorder] int NOT NULL
))') 

This seems to work without issue. Not always ideal as it requires changing the result set definition if the query changes but as these are bi reports they are likely using the same output scheme for most queries.

the second half of this is trying to get parameters to work.

i think that this would work if the declare statement is removed. bi doesnt seem to like thing like declare or with unless part of the dynamic sql which wont work in this case.

I assume the declare statement can be removed and @team replaced with a bi parameter. but i have not tried it yet.

declare @team nvarchar(max)= 'winet'
exec ('
SELECT *
FROM OPENQUERY ("snapserver", 
''EXEC specialprojects.dbo.CFE_DASHBOARD_Summary_BugTrendX @team='+@team+'
WITH RESULT SETS
(
( [rel] nvarchar(16) NOT NULL,
  [value] int NOT NULL,
  [year] int NOT NULL,
  [series] nvarchar(16) NOT NULL,
  [sortorder] int NOT NULL
))'')') 

[JackSprat]

Thanks for this reply.  I will try this today.  I posted previously but must not have hit save or something.  Thanks for the feedback.

[JackSprat]

This code executes as intended in Desktop, but fails in Power BI cloud.

DECLARE @CustomerNumberIDList dbo.IdList
Declare @Success int

INSERT INTO @CustomerNumberIDList
SELECT Distinct CustomerNumberId
FROM [dbo].[vw_ReportSummary]

EXEC @Success=[dbo].[LogCustomerView] @CustomerNumberIDList

If @Success=0
SELECT *
FROM
[dbo].[vw_ReportSummary]

My requirement is to log the key values anytime a customer is used in a report for privacy resason.   The portion that does the logging: 'EXEC @Success=[dbo].[LogCustomerView] @CustomerNumberIDList' works like a charm whenever I refreesh in Power BI desktop, but doesn't log the key values when I run the exact same report after uploading it to the cloud.  It does still get the dataset though.

An y ideas on why that call to log the keys only works in desktop?

[nirajdubey]

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

[Zak2815]

@nirajdubey, I can't for the life of me get this to work for Direct Query mode:

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

I've tried each of these below:

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

SELECT *
FROM OPENQUERY ([server name],
'EXEC dbname.dbo.spname @AsOfDate = ''03-16-2017 23:59:59'' ');

SELECT *
FROM OPENQUERY ([server name],
'EXEC dbname.dbo.spname @AsOfDate = CONVERT(DATETIME,''03-16-2017 23:59:59'',101) ');

None seem to work.  The times I can get a preview loaded, there's always an error on the "Apply".

Please help if anyone has succeeded in calling a Stored Proceedure in Direct Import Mode.

[ZhiqiangZhou]

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

[stangellapally]

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

[Seth_C_Bauer]

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

[bathory666]

Muchas gracias!! funciona perfecto !  

[nirvana_moksh]

@Seth_C_Bauer

I know this is an old post, but what's the use/benefit of using:

DECLARE @sqlCommand varchar(1000)

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

Opposed to a plain and simple:

EXEC STORED_PROCEDURE

[Seth_C_Bauer]

@nirvana_moksh At the time of the previous post when you ran a stored procedure against the database in Direct Query mode it woud fail. This work around got it to work, I have not tested it in awhile, so I don't know if that behaviour has changed. If you use "import" then you can just use a straight execute statement.

[nirvana_moksh]

@Seth_C_Bauer - thanks for the reply Seth!

[prasad_chengti]

Hi,

I know this post might have been closed as the solutions provided here is working for many. 

However when I am trying to follow same steps getting different kinds of errors.

We were fetching the data from remote database(ORACLE) through VPN in Power BI using Server Name and Connection String.

Using below mentioned syntax, writing the query in Import section and calling the stored procedure which is created in remote database. But when we try to run the report getting the error as follows. To be double sure the stored procedure is not having syntax error or such, executed the same procedure in sql developer and it works as expected. Any help or lead on this is highly appreciated.

Query1: 

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

For the above query, the error msg is:

Query2:

DECLARE @sqlCommand varchar(1000)   ==>  DECLARE @Variable varchar(1000)

SET @sqlCommand = 'dbo.Testproc'        ==>   SET @Variable = 'dbo.nid_poc_test1'
EXEC (@sqlCommand)                                ==>   EXEC (@variable)

For this query the error msg is:

Unable to connect

We encountered an error While trying to connect.

Details: "Oracle:ORA-06550:line 3,column 5: PLS-00488:'variable' must be a type ORA-06550:line 3,column 5:
PL/SQL: Item ignored
ORA-06550:line 5,column 1:
PLS-00221:'variable'is not a procedure or is undefined

ORA-06550:line 5,column 1:
PL/SQL: "Statement ignored"

Error2 while using semicolon at the end:

 DECLARE @Variable varchar(1000);

SET @Variable = 'dbo.nid_poc_test1';

EXEC (@variable);

Here VPN is very much connected and stored procedure was pretty much running fine in remote DB. We have also tried giving the command time out in Power BI. 

[sfiros2003]

Its very useful, thank you all.

My question is how to pass parameter value from a list or report as per some selection (not hard-coding in query).

[premjitsaha]

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

[Seth_C_Bauer]

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

[premjitsaha]

thanks it worked

[radz]

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

[Seth_C_Bauer]

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

[dsilveira]

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