Forum Discussion
How to use a SQL Server Stored Proc (with no parameters) as a datasource ?
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
In SQL can you create a view that calls the stored proc then hit the view from Power BI?
14 Replies
- jdbuchanan71
Super User
In SQL can you create a view that calls the stored proc then hit the view from Power BI?
- jdbuchanan71
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.
-
gluizqueirozResolver I
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:- gluizqueiroz
Could you try "Import" type and use "EXEC procedure" instead "EXECUTE procedure"?
(I use Power BI in Portuguese)Return:
Dharmendharan_LHelper I
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.
ThanksDharmendran