Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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
SOLVED on my side at least: Make sure that you are getting data in Import Mode (not Direct Query!)
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
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.
Did you solved your issue yet? If yes, how did you resolve it?
I think you might need to do like this;
StartDate = Text.From(RangeStart),
EndDate = Text.From(RangeEnd)
Thanks,
pthapa
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
User | Count |
---|---|
95 | |
86 | |
78 | |
72 | |
66 |
User | Count |
---|---|
113 | |
105 | |
84 | |
65 | |
64 |