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

fmunar1985's avatar
fmunar1985
Frequent Visitor
4 years ago

you have to put Import instead of DirectQuery 😉 

  • 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

      • Anonymous's avatar
        Anonymous
        Not applicable
        2 years ago

        @Rena I have store procedure which have 3 parameters, can you please help me how to write the same in advance editor.