Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount.
Register nowThe Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.
Ran into an issue while trying to use a SQL Server Stored Procedure as a data-source in Power BI desktop. I get the error "Microsoft SQL: Incorrect syntax near the keyword 'EXEC'.". Kind of ironic that an error is encountered with Microsoft's own DataBase product. I have tried both "Direct Query" & "Import" options with the exact same end-result. I am able to EXECute the Stored Proc just fine within SQL Server Management Studio with the exact same userid that I use in Power BI. Appreciate any help
Thanks
Jagannathan Santhanam
Solved! Go to Solution.
In SQL can you create a view that calls the stored proc then hit the view from Power BI?
Hi All,
Is there any update regarding this issue ?. Im finding the same problem, Stored procedures can not be used in Direct Query but works with Import mode.
If anyone found a solution regarding this issue, please share your idea to overcome this.
Thanks
Dharmendran
Yes, you can use a stored procedure as "datasource" IF the stored procedure returns a table. I have created this simple test and it works:
CREATE TABLE TableTest
(
NAME VARCHAR(100),
AGE INT
)
INSERT INTO TableTest
VALUES
('Jonh Doe', 20),
('Tom Wayne', 35),
('Tony Clark', 40)
SELECT * FROM TableTest
CREATE PROCEDURE ProcedureTest
AS
BEGIN
SELECT * FROM TableTest
END
EXEC ProcedureTest
If I execute this SQL code on SQL Server Management Studio it will return 3 lines with 2 columns containing the content of TableTest.
If you go to Power BI and go to "Get Data", choose "SQL Server Database" and put the IP, click on bottom button to "Advanced Options" and put EXEC ProcedureTest in the field for "SQL Instruction" it will load and show the return of procedure, if the return is a table, you will see a table. Like the following:
Same issue, I have the latest version of PBI Desktop "Version: 2.82.5858.961 64-bit (June 2020)"
Could you try "Import" type and use "EXEC procedure" instead "EXECUTE procedure"?
(I use Power BI in Portuguese)
Return:
Well, if my understanding of import mode is correct, it defeats the whole purpose of a live "Direct Query". I don't want to manually refresh data every time the data changes in the database, which is every minute, every day.
So, you can use Direct Query and copy the select statement (that is inside procedure) and paste it on Power BI, instead use "EXEC procedure".
Use SP_HELPTEXT 'YourProcedure' on SQL Server Management Studio > Copy the sql statement between BEGIN and END > Paste it on SQL Statement field on Power BI > Choose Direct Query
It should works, but maybe is necessary make some changes on sql code.
I could as well embed SQL in my direct connection, however, it defeats the whole purpose of code encapsulation. If I need to change the code, I don't want to do it in Power BI, it should be done at the SQL Server level.
BTW, I implemented a view using the SQL and it works. This is for sure an enhancement for the Microsoft Power BI developer team
In SQL can you create a view that calls the stored proc then hit the view from Power BI?
I don't think a VIEW can execute a stored procedure within itself. I have tried several types of commands and encountered compilation errors. BTW, my original post seems like an area of improvement for Power BI development team
Correct, you cannot call a sotred procedure from a view. What you can do is have the stored procedure write to a table and then schedule a SQL Agent job to run the stored procedure at your desired interval and then just use that table as your PBI data source.
I found the following that might help.
https://intellipaat.com/community/3763/how-to-use-sql-server-stored-procedures-in-microsoft-powerbi
In order to execute stored procedure in Power BI:
Go to SQL Server “right-click” on stored procedure and select “Execute”. After execution of your cod, a new query window opens up which was responsible for execution. Copy that Query.
Go to Power BI Query Editor, Click on New Source and select SQL Server. After giving the server and database, click on "Advanced Options", paste the query in the "SQL Statement" . Navigate using full hierarchy and click ok.
You will see data for the parameters you passed in Stored procedures only.
Remember that this works on "Import Query" option.
This is too clever. Thank you so much!!!!!
Already tried that, I only see Tables and views. I don't see Stored Procedures, although the specific Stored Proc was GRANted the correct rights.
User | Count |
---|---|
123 | |
69 | |
67 | |
58 | |
52 |
User | Count |
---|---|
185 | |
92 | |
67 | |
62 | |
52 |