cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Haleakala
New Member

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

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!

7 REPLIES 7
Haleakala
New Member

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.

 

Best Regards,
Winniz

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?

 

vkkfmsft_0-1639730080165.png

 

Best Regards,
Winniz

bcdobbs
Resident Rockstar
Resident Rockstar

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
Resident Rockstar
Resident Rockstar

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
New Member

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.

 

Haleakala_0-1638909318999.png

 

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
Community Support
Community Support

Hi @Haleakala ,

 

Are you using the following native queries? 

 

vkkfmsft_0-1638778993455.png

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?

 

vkkfmsft_1-1638779223873.png

 

Best Regards,
Winniz

 

Helpful resources

Announcements
2022 Release Wave 1 760x460.png

2022 Release Wave 1 Plan

Power Platform release plan for the 2022 release wave 1 describes all new features releasing from April 2022 through September 2022.

Power BI December 2021 Update_carousel 768x460.jpg

Check it Out!

Click here to read more about the December 2021 Updates!

User Group Leader Meeting January 768x460.png

Calling all User Group Leaders!

Don't miss the User Group Leader meetings on January, 24th & 25th, 2022.

Jan 2022 Dev Camp 768x460 copy.png

Power BI Dev Camp- January 27th, 2022

Mark your calendars and join us for our next Power BI Dev Camp!