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.
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!
I voted .
I did my research and found a solution use the following query (replace server name and parameters you would like to use):
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)