Forum Discussion

TahmidBari's avatar
TahmidBari
Helper I
3 years ago

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

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

General Comment
Interesting Links
Need Help
Show and Tell
Tips and Tricks
Tutorial Requests

5 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    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

  • TahmidBari's avatar
    TahmidBari
    Helper I
    3 years ago

    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.

     

    • pthapa's avatar
      pthapa
      Post Patron
      2 years ago

      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

       

  • mwroblewski's avatar
    mwroblewski
    Frequent Visitor
    3 years ago

    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

  • mwroblewski's avatar
    mwroblewski
    Frequent Visitor
    3 years ago

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