I have a report which uses SQL Server Import. The report refreshes fine in the Desktop, but when I publish it to Power BI service it runs a refresh for about 45 minutes then fails with no error message. I am not using incremental refresh or mixed mode. We have an on premise gateway that is running and online and other reports are refreshing without issue agains the same SQL server and gateway.
One unique thing about this report is that I am leveraging a native database query performs a left outer join with another table and selects specific fields. There are no errors with this, but perhaps it's causing the refresh to fail in the service? The join is a bit complicated and I couldn't replicate it in Power Query/Data Model. Anyone have experience in this area or suggestions for troubleshooting?
A manual refresh in the service does not work. I've updated the Gateway to the latest version, it is working to refresh my other reports.
My workspace is not PPU, but I created a new Pro workspace anyway and uploaded a new version of the dataset, still no luck. I wonder if the SQL server has some type of security with the native DB query through the Gateway.
Hi @Haleakala ,
Has your problem been solved? If it is solved, please mark a reply which is helpful to you.
If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.
Is the gateway datasource using the same credentials as power bi desktop? Eg if you're using windows authentication it might not be.
If it's not try running the query in SSMS with the same login (can wrap query in EXECUTE AS LOGIN = 'DOMAIN\Account'; <Your Query>; Revert;
Another thing to try is loading SQL Server Profiler, starting a trace and then refreshing your dataset in the service. It might let you see if it's a sql server issue or not.
Something else to try. Oddly I've just had a very similar sounding issue this morning.
Nothing was even reaching the SQL server when watching profiler so concluded it was service side.
Workspace was in PPU but dataflows were simple loads on prem DW eg no need for compute. I dropped the workspace out of premium and everything has refreshed. I've now restored it to being a PPU workspace and everything is now working again.
Can't even start to explain it - will put my own post in on forum.
Hi! Thank you for the reply. We just updated to the latest gateway and it works to refresh our other reports.
Unfortunately the error message is empty and the refresh takes a long time before it fails. Both manual and scheduled refreshes fail in the service. But a manual refresh on Desktop works without issues.
For the SQL query, I entered it using the method you descibed, and in Power Query editor it shows as the below format (but contains the actual real query)
Source = Sql.Database("Server-Name", "DatabaseName", [Query="select * from Table"])
Hi @Haleakala ,
Are you using the following native queries?
What is your gateway version? Could you please manually refresh successfully in the service? Are there any error messages associated in the refresh history of this dataset?
Power Platform release plan for the 2022 release wave 1 describes all new features releasing from April 2022 through September 2022.
Click here to read more about the December 2021 Updates!
Don't miss the User Group Leader meetings on January, 24th & 25th, 2022.
Mark your calendars and join us for our next Power BI Dev Camp!