Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Can I execute a SP in DirectQuery Mode with or without parameters?

Hello everyone,

 

Many thnaks in advance!


I was trying to build a simple live report using DirectQuery but I was keep getting errorrs when the Stored Procedure was being executed and I was getting the following error message even though my syntax seemd to be ok


Perhaps, my syntax is not correct or what am I missing ?

 

 

Need Help
  • Anonymous's avatar
    Anonymous
    4 years ago

    Hi Anonymous ,

    Please review the content in the following blogs and check whether they can help you achieve the requirement.

    The first method:

    Power BI DirectQuery with Parameterized Stored Procedure

    1. Please set the source as below in Advanced Editor:

     

    = Sql.Database("xxxxx", "AdventureWorksDW2014", [Query="SELECT * FROM #(lf)OPENROWSET('SQLNCLI','trusted_connection=yes', 
     'exec AdventureWorksDW2014..getProcategory')", CreateNavigationProperties=false])

     

    2. By default, SQL Server does not allow ad hoc distributed queries using OPENROWSET and OPENDATASOURCE. So you will get the below error message. When this option is set to 1, SQL Server allows ad hoc access. Then you can connect to sql server successfully...

     

    sp_configure 'show advanced options', 1;  
RECONFIGURE;
GO 
sp_configure 'Ad Hoc Distributed Queries', 1;  
RECONFIGURE;  
GO

     

    ad hoc distributed queries Server Configuration Option

    The second method:

    SQL Server and Power BI: How to load Stored Procedure data into SQL Server with DirectQuery

    Best Regards

8 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    4 years ago

    Anonymous , in SQL server we do not give = when execute a proc

     

    example

    EXEC sp_procoption

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      Hi Amit, thank you for your reply.
      I did used this syntax now EXEC dbo.Settings_Sage_Companies and I am still getting the syntax error

      Any thoughts on this one?

       

      • Datatum's avatar
        Datatum
        New Member
        2 years ago

        Hi Anonymous did you find a solution for this? 

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Perhaps I should rephrase it or to give more details

    1 - I have this simple stored procedure 

     

     

    2 - I am trying to add this SP to my PBI desktop and to import it using the DirectQuery mode

     

    3 - Data loads perfectly but in the moment I am applying those changes I am getting errors

     

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      Thanks Munar, it works perfectly with the Import connectivity mode, but I need my data to be live not cached... That's why I was looking for a solution here. Any other thoughts on this one?

      • Anonymous's avatar
        Anonymous
        Not applicable
        4 years ago

        Hi Anonymous ,

        Please review the content in the following blogs and check whether they can help you achieve the requirement.

        The first method:

        Power BI DirectQuery with Parameterized Stored Procedure

        1. Please set the source as below in Advanced Editor:

         

        = Sql.Database("xxxxx", "AdventureWorksDW2014", [Query="SELECT * FROM #(lf)OPENROWSET('SQLNCLI','trusted_connection=yes', 
     'exec AdventureWorksDW2014..getProcategory')", CreateNavigationProperties=false])

         

        2. By default, SQL Server does not allow ad hoc distributed queries using OPENROWSET and OPENDATASOURCE. So you will get the below error message. When this option is set to 1, SQL Server allows ad hoc access. Then you can connect to sql server successfully...

         

        sp_configure 'show advanced options', 1;  
RECONFIGURE;
GO 
sp_configure 'Ad Hoc Distributed Queries', 1;  
RECONFIGURE;  
GO

         

        ad hoc distributed queries Server Configuration Option

        The second method:

        SQL Server and Power BI: How to load Stored Procedure data into SQL Server with DirectQuery

        Best Regards