Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
adonovan
Frequent Visitor

Receiving intermittent "invalid query locator" when refreshing Salesforce Object

Hi all

 

I have a report that is set to auto-refresh during the day. 

Intermittently, at different intervals, the refresh will fail with "invalid query locator" for the same Salesforce Object each time. The next automatic refresh will work by itself without any intervention. If I see the refresh failure and manually refresh on the website, then the refresh also succeeds without issue. 

 

Failure details:

invalid query locator. The exception was raised by the IDataReader interface. Table: Survey Taken.

 

There are 7910 rows (currently) returned by this object, so it is not especially large. 

Any ideas why this is happening and how to work around it? 

Any further information required? 

8 REPLIES 8
Jitmondo
Helper III
Helper III

Still receiving the invalid query locator message - did anyone have a concrete fix to this issue ?

 

kdlong
Frequent Visitor

Hi All, 

I have come across this error as well. After weeks of 24 refreshes a day, with only the odd error, my refresh times ballooned and quickly began to fail (going from ~15 mins to 60 mins). Here are all the potential causes I've come across:


1) You have initiated more than 10 Saleforce queries at once

  - See: https://help.salesforce.com/articleView?id=000323582&type=1&mode=1

  - Based on my experience, this can be caused if you have more than one refresh referencing Salesforce objects at the same time. To trouble shoot, only have 1 refresh going.

 

 2) Your cache in your desktop file is somehow interfering with the refresh

  - Unsure how this would impact everything, but I did clear my cache and republished the data model 

  - I do not believe this changed anything with my refresh success

 

3) Same Salesforce object interacting

  - This turned out to be my error. 

  - Here's what happened: I queried a Salesforce object (lets call in DimBase) then referenced DimBase in two subsequent tables (DimA and DimB).

  - I then merged DimB to DimA in a way that permited query folding

  - I'm definitely not an SQL expert but this query being sent back to Salesforce (via folding) very likely was the cause. Someone with more experience in SQL could likely explain the cause of this better. 

  - Once I changed this merge, the error no longer occurred and my refreshes resumes pre-error speeds / success rates. 

 

The head scratcher for me was why is started occurring now, and not previously. It might have something to do with load on my the Power BI service and the query locators (see #1) expiring, but I'm unsure. 

 

Hopefully this helps some users with this very frustrating issue. 

Cheers

 

K

 

PS - on a side note, is Microsoft ever going to sort out pulling picklist values rather than Saleforce api's? Asking for a friend... 

kdlong
Frequent Visitor

Hi All, 

 

After I posted the above, I am still receiving intemittent errors, although not at the same frequency as before. I do believe that #3 of the above post was the primary reason for the errors; however, I did notice another issue that might have contributed.

 

As I was working through the queries and trouble shooting, I did notice that in the query editor I was getting an error of "Invalid Query Locator. Details: [List]". When I refreshed the preview, this went away so I didn't give it much attention. After significantly clean up my query, I was still getting this and I have traced it back to a object which is being merged with the object of issue. Within the object being merged, I was using a find and replace for some blank fields. When I removed this, the invalid query locator error no longer occurred. 

 

I believe it has something to do with how Power BI constructs the SQL query for replace values, where completing this action will significatly impact performance.

 

This post somewhat explains why: https://stackoverflow.com/questions/22476662/sql-replace-statement-too-slow 

 

Hopefully this helps some of you. 

 

Cheers

Anonymous
Not applicable

Hello there! any solution about this topic? I'm suffering this error too

andreamastrini
Frequent Visitor

Hello. We ar facing the same issue.. is there any solution for it? Thanks

Anonymous
Not applicable

Hi,

 

I also have a similar issue. It was behaving like an intermittent issue last couple of days. But now, on demand refresh also fails.

However i could refresh it locally.

 

Any updates you have on this issue?

Thanks in advance.

 

Soumya

v-juanli-msft
Community Support
Community Support

Hi @adonovan 

When refresh failures, is there any visual broken on the report?

Could you refresh from Power BI Desktop successfully?

 

As searched, there is an article you could refer to

Power BI Error : “The exception was raised by IDataReader interface.”

 

Best Regards
Maggie

 

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

Hi @v-juanli-msft 

 

Apologies, I thought I had email notifications turned on, but I missed your response. 

Visuals are not broken when the refresh fails, I just see old data.

As I mentioned in my post, this error occurs intermittently with scheduled refresh in Power BI (published to powerBI web) and then when I manually refresh it, it succeeds with the refresh. If I dont manually refresh, the next scheduled refresh works by itself. It generally does this every day or two. 

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors