Skip to main content
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 

Did you hear? There's a new SQL AI Developer certification (DP-800). Start preparing now and be one of the first to get certified. Register now

chriscooper71

BUG: VNET Gateway Issue

Submitted by on ‎04-17-2023 12:06 PM

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

See more ideas labeled with:
59 Comments (59 New)
Comments
Pau_MDW
New Member
‎05-09-2023 01:45 AM

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
‎05-09-2023 02:31 AM

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
‎05-09-2023 03:33 AM

Thanks for the region info 🙂

Anonymous
Not applicable
‎05-09-2023 03:58 AM

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
‎05-09-2023 05:04 AM

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?

Anonymous
Not applicable
‎05-09-2023 05:10 AM

@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)

Anonymous
Not applicable
‎05-09-2023 09:33 PM

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
‎05-10-2023 05:19 AM

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
‎05-10-2023 05:26 AM

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
‎05-10-2023 06:33 AM

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

Idea Statuses