Forum Discussion

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

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

Icon for Resolver I rank

Resolver I

6 years ago

Could you try "Import" type and use "EXEC procedure" instead "EXECUTE procedure"?
(I use Power BI in Portuguese)

Return:

Icon for Super User rank

Super User

6 years ago

gluizqueiroz is correct, it only works with import mode, not direct query.

Anonymous
Not applicable
6 years ago
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.
- gluizqueiroz
  Resolver 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
    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

Related content