Refresh not working - no error message (Native DB Query)

[Haleakala]

Hi everyone!

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?

Thanks!

[Haleakala]

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.

[v-kkf-msft]

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.

Best Regards,
Winniz

[v-kkf-msft]

Hi @Haleakala ,

Do you have any of the messages shown below in your failed refresh history? Could you please share them? I will try to see if I can use this information to find the specific error message.

If you use a personal gateway, can the dataset refresh successfully?

Best Regards,
Winniz

[bcdobbs]

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.

[bcdobbs]

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.

[Haleakala]

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)

let
Source = Sql.Database("Server-Name", "DatabaseName", [Query="select * from Table"])

in

Source

[v-kkf-msft]

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?

Best Regards,
Winniz