PowerBI Desktop native query from SQL Server getting error: Microsoft SQL: Incorrect Syntax

[csw2002]

I am very new to Power BI desktop (or Power BI in general), though I am very experienced developing on SQL Server for over 20 years. Hence, my initial experience with PowerBI is basically to convert Microsoft Query/Pivot Tables to Power Query/Power Pivot in Excel. I have been using native queries with no issues at all in Excel based Power BI environment. However, at the very first time I am trying my hand on PowerBI desktop, I have run into a road block. When I try to invoke a stored proc using direct query (from Get Data -> SQL Server screen), data initially return just fine - until I hit load button. Then I will receive "Microsoft SQL : incorrect syntax near the keyboad ..." where ... depends on the script I am trying to execute (a stored proc usually means ... is Exec. If I use a normal select query, the load process returns successfully. Surely, PowerBI desktop would allow an execution of stored proc via direct query. I am just stumped as to exact the configurations that I need to make to allow execution of SQL script (be it stored proc or not) directly 

[csw2002]

I think I have figured out the issue. It looks like PowerBI desktop does NOT allow execution of stored proc in a direct query. I will have to use import data instead of executing a stored procedure. The reason that I want to use the stored proc is that (1) the reporting logic is fairly complex to be wrapped up in a view or select statement and (2) the underlying table has billions of records and each query would only return hundreds of records. Hence it makes far more sense to run the stored proc (based on parameter selection made by user) than importing billions of records and let user filter by parameter.

I am aware that I probably can use tricks such as select from openrowset to get the data out. However, that brings in its own set of issues (such as needing to imbed security details within the openwroset function). Surely there is a way in PowerBI that would allow me to execute and retrieve data on command???

[amitchandak]

@csw2002 , Hope you are running like this

https://databear.com/sql-stored-procedures-in-power-bi/

Also check is it working in import mode. Need to check if it is supported in direct query

[csw2002]

Thanks for the response. As I stated in the 2nd post, I can get stored proc working only by importing the data as this article suggests. However, also as explained in my 2nd post, this method is really not satisfactory due to the large underlying data (billion+ records) and fairly small return data per report (a few hundred records).