Calling a sproc using Incremental Refresh in PBI - MS SQL: Incorrect syntax near the keyword 'Exec'

[TahmidBari]

Hi PBI Team,

I was going through a video (GIAC) on Calling a Stored Procedure using Incremental Refresh in Power BI? By doing so, I am able to fold the following NativeQuery however, I am seeing an error message while loading the data into pbi desktop. Microsoft SQL: Incorrect syntax near the keyword 'Exec'. Must declare the scalar variable "@StartDate". Could you please let me know if there is anything wrong with my NativeQuery? Thanks.

let
Source = Sql.Database("server_name", "db_name"),
NativeQuery = Value.NativeQuery(Source, "Exec rpt_TestSproc @StartDate, @EndDate", [StartDate = RangeStart, EndDate = RangeEnd], [EnableFolding = true] )
in
NativeQuery

YouTube (GIAC): https://www.youtube.com/watch?v=-KXDpi_wyD4

[mwroblewski]

SOLVED on my side at least: Make sure that you are getting data in Import Mode (not Direct Query!)

[mwroblewski]

I was following the same video to test this apprach and I'm ending up with exactly same errror. In Power Query editor data loads without a problem, but one closing and applying it breaks. Some peaple were advising to update PowerBI Desktop to the newest version, but it didn't work on my end. Please, let me know if you find a root cause. But seems like a PBi bug or so

[TahmidBari]

Hi @Xiaoxin Sheng,

Thank you for the note. I am connected to the dataset through import mode. Please be informed that @StartDate and @EndDate are the sproc parameters and I have created RangeStart (date/time) and RangeEnd (date/time) parameters in the power query editor. Unfortunately, your M query is not working. Thanks.

[v-shex-msft]

Hi @TahmidBari,

I think these t-SQL statements should be added into the data connector steps instead of navigation step. You can parameterized it with your query parameters.

let
Source = Sql.Database("server_name", "db_name", [Query="Exec rpt_TestSproc "& RangeStart&", "& RangeEnd ])
in
Source 

BTW, which connection mode are you worked? AFAIK, the 'Stored Procedure' not able used in the SQL statement when you are design report with direct query mode.

Regards,

Xiaoxin Sheng