BUG: VNET Gateway Issue

[chriscooper71]

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)

[Pau_MDW]

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]

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]

Thanks for the region info 🙂

[Jeff__]

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]

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]

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

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]

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]

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]

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