Forum Discussion
Has the ability to refresh a dataset with a dynamic SQL stored procedure been disabled?
Microsoft have solved this on the backend and it is working again.
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
Resultinstead 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 :(
Super UserHi 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
ResultTo ensure that it is only passing a Date and not DateTime?
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.
- GilbertQ
Hi Anonymous
Pity I was hoping that might be the work around.
SQL Profiler basically returns everything happening on the database. So you are able to see firstly if the query is getting to the database. As well if it is, what exactly it is sending to SQL Server.
If you have a DBA and explain that you want a trace from Power BI, and co-ordinate a time I am fairly certain they will be able to give you the results.
Thanks GilbertQ. I'll get that organised. Any luck it will turn up some new detail. My gut instinct is still that MS have changed something on us, but it is always prudant to get all of the data together.