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

[criss317]

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 ?

[fmunar1985]

you have to put Import instead of DirectQuery 😉 

[criss317]

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?

[v-yiruan-msft · Accepted solution]

Hi @criss317 ,

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]

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

[criss317]

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

[amitchandak]

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

example

EXEC sp_procoption

[criss317]

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]

Hi @criss317 did you find a solution for this?