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?

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Same issue, I have the latest version of PBI Desktop "Version: 2.82.5858.961 64-bit (June 2020)"

 

 

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

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

Return:

 

  • jdbuchanan71's avatar
    jdbuchanan71
    Icon for Super User rankSuper User
    6 years ago

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

    • Anonymous's avatar
      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'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.