Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
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.
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. |
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.
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.
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Check out the June 2024 Power BI update to learn about new features.
User | Count |
---|---|
93 | |
87 | |
80 | |
69 | |
68 |
User | Count |
---|---|
226 | |
129 | |
119 | |
83 | |
77 |