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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

BUG: VNET Gateway Issue

Standard PowerBI Gateway has no issues refreshing a dataset but when using the VNET Gateway we recieve this error:

 

Microsoft SQL: The size (8000) given to the convert specification 'nvarchar' exceeds the maximum allowed for any data type (4000).\r\nThe size (8000) given to the convert specification 'nvarchar' exceeds the maximum allowed for any data type (4000)

Status: Delivered

Hi all,

The deployment should be all completed now.

 

Best regards,

Community Support Team_yanjiang

Comments
Pau_MDW
New Member

Hi all, we're experiencing the same scenario and error: Europe, Serverless, and Import mode. We have columns of type NVARCHAR(255) undergoing transformations in Power Query, which then merge with other VARCHAR columns.

Our current workaround involves replacing these transformations with SQL queries that precisely replicate the Power Query transformations. This approach has been successful, and we're now able to refresh without any errors. We'll leave that until Microsoft reports that the bug has been fixed.

Jannematz
Frequent Visitor

MSFT got back at me:

As we checked with our product team they had triggered a Bug file on that issue and working to fix the issue on priority. As per their response and based on your Power BI tenant region the ETA of the Bug fix will be 12th May 2023.  So, can you please check the issue after the Bug fix date and let us know if you are still facing the issue.

 

Our powerbi tenant region is West Europe (Netherlands).

Still_Lukad
Regular Visitor

Thanks for the region info 🙂

Jeff__
Regular Visitor

Hi @All,

Thanks for the very helpful info about this issue.

 

Since this is a live issue and MS is aware of it, is there any place where we could see all live issues?
The status page says "No known issues: Power BI is running smoothly", which is not true and even misleading.

 

 

mweaver44
New Member

Similiarly to Pau_MDW, we are experiencing the same issue here using SQL Serverless views form Azure Synapse and Import mode. We cannot refresh Power BI reports that have columns of type NVARCHAR(8000) and where there is transformation in Power Query.

 

We tried amending the sql views to cast the data type as varchar 1000 however this did solve the issue as Power Query appears to override this.

 

Our only workaround is to download the Power BI files from the workspace or access the pbix file from their saved location, refresh them in Power BI desktop and re-publish. At the moment, it looks like we will have to do this everyday on all reports until the issue is fixed.

 

Anyone have any other fixes?

alxdean
Advocate V

@mweaver44 , we have implemented following temporary workaround:

 

1) Identify all tables where a type conversion to text is being folded into the native query

2) immediately before the type conversion insert a new index column. then remove the colum, leaving you with 2 steps before the type conversion. 

 

this will break query folding at that point and the type conversion will happen in the service (and all subsequent steps too)

PowerAndy
Regular Visitor

sinse monday with have the same issue.
Europe West.
On Synapse SQL serverless all data type with varchar(8000) changed to under 4000.

That was for nothing, because we still get the same error message.

YayaLouiz
Regular Visitor

Since 05/05/2023, I have been experiencing the same issue in all of my reports. The scheduled refresh is failing in the Power BI service, but manual refresh works fine in Power BI Desktop.
I am using Synapse SQL Server as the data source. I am unsure if the problem is related to the SQL server or if it's a bug in Power BI service

. The error message I receive is: "The size (8000) given to the convert specification 'nvarchar' exceeds the maximum allowed for any data type (4000)".

Still_Lukad
Regular Visitor

What I still do not understand:

The original Issue was reported in April 2023 and got fixed last month. Yet, this issue is relevant again. How is it possible at Microsofts end that previously fixed bugs show up again after being fixed?

YayaLouiz
Regular Visitor

@alxdean 
Thanks for your workaround.
I tried it and it worked very well for me