Showing results for 
Search instead for 
Did you mean: 

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

Helper III
Helper III

Power BI dataset refresh fails in Service when using merge between Azure SQL DB and On-prem SQL DB

Hi all,

When I am using merge query between Azure SQL DB and On-premises SQL DB then power bi dataset refresh fails in service for on-demand as well as scheduled. 
2 data sources are used - Azure SQL DB and On-premises SQL DB 

Data source error{"error":{"code":"DM_GWPipeline_Gateway_MashupDataAccessError","pbi.error":{"code":"DM_GWPipeline_Gateway_MashupDataAccessError","parameters":{},"details":[{"code":"DM_ErrorDetailNameCode_UnderlyingErrorCode","detail":{"type":1,"value":"-2147467259"}},{"code":"DM_ErrorDetailNameCode_UnderlyingErrorMessage","detail":{"type":1,"value":"Timeout expired. The timeout period elapsed prior to completion of the operation."}},{"code":"DM_ErrorDetailNameCode_UnderlyingHResult","detail":{"type":1,"value":"-2147467259"}},{"code":"Microsoft.Data.Mashup.HostingError.Reason","detail":{"type":1,"value":"Timeout"}}],"exceptionCulprit":1}}} Table: Case.

NOTE : When I remove merge on-prem DB from azure SQL DB then refresh wokrs fine including refrreshing on-prem tables .

I would like to highlight the below which might give some backgroud:

- Gateway has been setup already and works fine for other dataset (gateway server is up to date) except merge 

- Dataset refresh works fine in desktop and get refreshed in 2 mins 
- Tried changing the privacy settings and sometime it worked using Ignore privacy level but again failing (no changes in dataset)
- Both data source has privacy setting set as Organizational
- Tried increasing command timeout to 90 minutes in both data sources but no luck 
- Dataset size is very less of 2 MB . Usually, it get refreshed in 2-3 mins max
- Parameter is used for Azure SQL DB to ease deployment
- Also tried disconnecting the VPN while refeshing the dataset

I have opened MS ticket as well but didn't get solution yet and also shared the gateway logs while refrshing the dataset . The ticket is still open and its been more than 15 days now. Support team have engaged porduct team but they will not join call unless its prod issue and we need to just share logs and wait . 

I strongly feel that there is no issue with gateway as it works fine when merge is not being used.

Please let me know if anyone has come across such scenario and what was the fix applied to the problem

Thanks in advance!

Super User
Super User

yeah, that error message is suspicious. I think it masks the actual issue.  Keep working with Microsoft's SI on the Pro ticket, they will resolve it eventually.  Please keep us in the loop on what you find.

Helpful resources

PBI November 2023 Update Carousel

Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

Community News

Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

Dashboard in a day with date

Exclusive opportunity for Women!

Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!

Power BI Fabric Summit Carousel

The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors