Native Queries All of a Sudden stopped refreshing in Service only

[dbrandone]

Starting about 3-4 weeks ago, I would have 10% of my daily refreshes fail on the reports using a Value.NativeQuery query. According the refresh failure details, the error details include: "native queries aren't supported by this value". Nothing in the native query script has changed in over a year. The native query script is:

IF EXISTS (SELECT * FROM dbo.InHouse WITH (UPDLOCK) WHERE InventoryDate = '16/05/2024')
SELECT * FROM dbo.Inhouse
ELSE INSERT INTO dbo.Inhouse (InventoryDate, InHouseTotal) VALUES ('16/05/2024',179)
SELECT * FROM dbo.InHouse

Any ideas on what has changed recently or what could be the issue as to why this may be happening. I have checked settings and confirmed that the security option for native query permissions is unchecked so it should pass through. 

[dbrandone]

Hi @v-nuoc-msft,

I appended [EnableFolding=false] and it works in desktop but still not in the service. I also read that there were some gateway issues starting in March 2024 that could have lead to this issue. I reverted back to the Feb 2024 gateway and still have the same issues. Here is the Error I am getting in the service for the refresh.

{"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":"Native queries aren't supported by this value."}},{"code":"DM_ErrorDetailNameCode_UnderlyingHResult","detail":{"type":1,"value":"-2147467259"}},{"code":"Microsoft.Data.Mashup.ValueError.Detail","detail":{"type":1,"value":"#table({\"Name\", \"Data\", \"Schema\", \"Item\", \"Kind\"}, {})"}},{"code":"Microsoft.Data.Mashup.ValueError.Reason","detail":{"type":1,"value":"Expression.Error"}}],"exceptionCulprit":1}}} Table: InHouse.

[lbendlin]

Writing back into the data source is generally not supported.  You have a safety in place, but maybe they started to enforce that a bit more.

[v-nuoc-msft]

Hi @dbrandone

In response to this question you posed, I looked up the following.

The solution offered by most people is:

Append the [EnableFolding=false] keyword to the native query.

You can view the link below for more details:

SnowFlake & PowerBI "native queries aren't support by this value" - Stack Overflow

Solved: Re: "Native queries aren't supported by this value... - Microsoft Fabric Community

Regards, 

Nono Chen 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.