Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

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 th...
Need Help
  • jdbuchanan71's avatar
    jdbuchanan71
    6 years ago

    In SQL can you create a view that calls the stored proc then hit the view from Power BI?

gluizqueiroz's avatar
gluizqueiroz
Icon for Resolver I rankResolver I
6 years ago

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.

Anonymous's avatar
Anonymous
Not applicable
6 years ago

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