Forum Discussion
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.
42 Replies
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''');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."
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)
nirajdubey Thank you. I succeeded in using the SP by OpenQuery as you introduced.
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.
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.
This code executes as intended in Desktop, but fails in Power BI cloud.
DECLARE @CustomerNumberIDList dbo.IdList
Declare @Success intINSERT 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?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 ))'')')Thanks for this reply. I will try this today. I posted previously but must not have hit save or something. Thanks for the feedback.
