Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago
Solved

Has the ability to refresh a dataset with a dynamic SQL stored procedure been disabled?

Up until today, I had a dataset that was able to be refreshed perfectly fine through the Service.  This particular query has a single database as a source, which is an Azure Database.  The dataset us...
Need Help
  • Anonymous's avatar
    Anonymous
    8 years ago

    Microsoft have solved this on the backend and it is working again.

Anonymous's avatar
Anonymous
Not applicable
8 years ago

GilbertQ  its a good suggestion, however refreshing through the desktop produces no issue.  The code is also unchanged since I created it early last month.

 

The only difference between today where it didn't work, and previous weeks where it had, was some simple changes to the front end reports.  I've not actually made any backend changes.

 

I've taken some code out into isolation and uploaded a dataset with just a single table, the ability to select a refresh in the service is still no longer there.

 

Something to note:  Whenever a refresh is required through the desktop, each new day does require a new set of authorized permissions.  This has always been the case, however it never caused any problems with the online side of this.  Part of me wonders if there is a security hole that has been patched?

GilbertQ's avatar
GilbertQ
Icon for Super User rankSuper User
8 years ago

Hi Anonymous

 

I think you are possibly right in that something has changed in the underlying Power BI Service and now this is affecting your refreshing of the data from the Azure SQL Database.

  • Anonymous's avatar
    Anonymous
    Not applicable
    8 years ago

    Interestingly,  If i change my functions fnStartDate & fnEndDate to return a literal, the problem also goes away.  It is only a problem if i use the dynamic portion of the function.

     

    So the problem goes away if I use:

    let
    Result = "'2020-01-01'"
in
    Result

    instead of:

    let
    Result = "'" & DateTime.ToText(DateTime.LocalNow(), "yyyy-MM-dd") & "'"
in
    Result

     

    I can only therefore conclude that something in the service is now actively looking for, and blocking, a truly dynamic query.  I was wondering if the functions themselves were enough to cause the problem.  It seems whatever has been put in place is more complex and quite intentional.

     

    Sadness :(

    • GilbertQ's avatar
      GilbertQ
      Icon for Super User rankSuper User
      8 years ago

      Hi there Anonymous

       

      Are you able to run Profiler on the source database?

       

      The reason that I ask is possibly your initial result is text based, and even though you are specifying the DateTime.ToText I wonder if that is actually happening.

       

      What happens if you change it from the original to

       

      let
    Result = "'" & Text.From(Date.From(DateTime.LocalNow(), "yyyy-MM-dd")) & "'"
in
    Result

      To ensure that it is only passing a Date and not DateTime?

      • Anonymous's avatar
        Anonymous
        Not applicable
        8 years ago

        Really quite a good suggestion GilbertQ, although it didn't pan out.  Same result.

         

        Can you give me some info on the profiler for the source database you are talking about?  Azure is still a little new to me, I haven't been given much access to know what tools are available.  I can get the Azure owner in our organisation to run it for me.