cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
prateekraina New Contributor
New Contributor

Calling SQL stored procedure with Direct Query

Hi Guys,

 

I was pretty stunned to know today that we cannot simply call a stored procedure in direct query mode as it gives systax error after closing Query Editor.

 

Can anyone provide any workarounds as i cannot switch to model based connection.

 

Prateek Raina

4 REPLIES 4
nickchobotar Established Member
Established Member

Re: Calling SQL stored procedure with Direct Query

Hello @prateekraina

 

 

Same here. I am able to load the data into PQ but once I close it I get back incorrect syntax error


image.png
image.png

 

image.png

Noak Member
Member

Re: Calling SQL stored procedure with Direct Query

Hi , @nickchobotar@prateekraina

I have the same problem did you find suitable solution?.

 

 

BR,
Noa.
nickchobotar Established Member
Established Member

Re: Calling SQL stored procedure with Direct Query

@Noak

 

Nope. Actually, there should not be a solution or a workaround to this. It should just work..... 

 

This issue is on the lis of PBI ideas (only 4 votes of today). Please vote!

 

https://ideas.powerbi.com/forums/265200-power-bi-ideas/suggestions/33504791-direct-query

 

 

 

 

 

 

 

 

N -

Noak Member
Member

Re: Calling SQL stored procedure with Direct Query

Hi @nickchobotar,

I voted .

 

I did my research and found a solution use the following query (replace server name and parameters you would like to use):

 

select *
from
openrowset('SQLOLEDB','SERVER=SERVERNAME;Trusted_Connection=yes;',' set fmtonly off;exec DATABASE..SPNAME @StartDate=''2018-01-01'' ,@EndDate=''2018-01-04'' ')

 

(you need to alow openrowset functionality at your sqlserver database)

BR,
Noa.