Error scheduling refresh after changing SQL Database connection to query workaround

[DataZoe]

I have a report/dataset that pulls in a number of tables from on prem SQL Database. I've gone back a couple times and changed the full load of the table to a SQL query, and each time I do this I get an error with the scheduled refresh. It shows the data gateway, I can pick it for each connection, but it keeps erroring when I try to actually refresh. There is also now two connections instead of on previously. Super frustrating! 

I figured out how to get around it, and I hope anyone else who runs into this can try this too. I figured I just needed a way to get it to forget the old connection. So, I created a new power bi desktop report, and just did "Enter Data" and put in a single cell of data. I saved this report with the same name as my dataset. Then I published it and replaced the dataset. I then updated the app. Then I opened the actual dataset in Power BI Desktop and re-published it, replacing the blank one. Again, updated the app. 

After I did this, then the scheduled refresh could be set up again with no issues and the report refreshes with no issues on the service. I had it set up with multiple times during the day and all those were still there too.

Hope this helps someone!

[v-easonf-msft]

Hi , @DataZoe  @lbendlin 

Well done.

Glad to learn new things here. 

Best Regards,
Community Support Team _ Eason
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[lbendlin]

What you probably didn't realize is that doing all this will most likely also change the dataset and report GUIDs.  That means all your report consumers and the ones that use Analyze in Excel will have to change the links that they most likely have saved (and shared with others)

[DataZoe · Accepted solution]

@lbendlinIt prompts to replace with each publish, so it doesn't change the GUIDs 🙂