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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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
v-yanjiang-msft
Community Support
Status changed to: Investigating

Hi @chriscooper71 ,

From the error message, please try to change the data type to nvarchar(max).

vyanjiangmsft_0-1681801710723.png

Reference: sql server - Handling more than 8000 chars in stored proc parameter - Stack Overflow

 

Best regards,

Community Support Team_yanjiang

 

chriscooper71
Regular Visitor

So the problem I have with that is it works with a normal gateway. The data source is a Synapse Delta table and the PowerBI data set  just loads tables.

chriscooper71
Regular Visitor

This issue just started Saturday 4/15 but was working before that. Here's a definition of the Delta Lake Table that is now throwing an error. Wondering if a change was made in the VNET code or deployment of something new over the weekend.

 

CREATE OR REPLACE TABLE presentation.qbr_policy_excesscasualtycore (
RatingPolicy_HK bigint ,
RatingUnderlyingCoverage_HK bigint ,
Policy_HK bigint ,
PolicyNumber string ,
TechnicalPremiumNonPRIMA string ,
InsuranceName string ,
InceptionDate int ,
ExpirationDate int ,
RateChange decimal(20,5) ,
RateChangeExclude string ,
BILRExclude string ,
PremiumChangeDueToExposure decimal(38,17) ,
BoundPremiumCurrent decimal(20,5) ,
TotalPremiumChange decimal(38,17) ,
Renewal_Eligible boolean ,
PureRateChange decimal(20,5) ,
ExpectedLossRatio_BILR decimal(38,8) ,
BoundToTechnical decimal(38,18) ,
Limit decimal(28,5) ,
Attachment decimal(20,5) ,
New_Renewal string ,
Underwriter string ,
Branch string ,
Premium decimal(19,2) ,
CommissionAmount decimal(19,2) ,
StatusID int ,
Status string ,
PP bigint ,
LT bigint ,
MT bigint ,
HT bigint ,
FleetIndex5Autos bigint ,
HazardGrade string ,
GL_hazard string ,
Insured_State string ,
ESCAPE_to_Core string ,
Parent_Broker string ,
Broker_Code string ,
Broker string ,
PremiumChangeDueToCoverage decimal(38,9) ,
Auto_Fleet_Index bigint ,
Total_Auto_Count int ,
GL_Index_Weighted double ,
Implied_Expiring decimal(38,16) ,
Expected_Loss string ,
TechnicalPremium string ,
UW_Year string ,
UW_Month string ,
UW_Quarter int ,
period string ,
Expiration_Year string ,
Expiration_Month string ,
ImpliedExpiringbeforerateatcurrentCovExp string ,
ImpliedExpbeforerateandbeforeCov string ,
ImpliedExpbeforerateCovandExp string ,
ATR string ,
Prem_WtdLimit decimal(38,6) ,
Prem_WtdAttachment decimal(38,6) ,
RecordSourcePolicy string ,
RecordSourceRatingPolicy string ,
RecordSourceRatingPolicyUnderlyingCoverage string ,
StartDate_C date ,
StartDate_P date ,
EndDate_C date ,
EndDate_P date ,
MaxInceptionDate int 
) USING DELTA;

 

 

Here's the whole error: 

  • Data source error{"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":"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)."}},{"code":"DM_ErrorDetailNameCode_UnderlyingHResult","detail":{"type":1,"value":"-2147467259"}},{"code":"Microsoft.Data.Mashup.ValueError.Class","detail":{"type":1,"value":"16"}},{"code":"Microsoft.Data.Mashup.ValueError.DataSourceKind","detail":{"type":1,"value":"SQL"}},{"code":"Microsoft.Data.Mashup.ValueError.DataSourcePath","detail":{"type":1,"value":"syn-core-data-spk-das-prod000-ondemand.sql.azuresynapse.net;presentation"}},{"code":"Microsoft.Data.Mashup.ValueError.ErrorCode","detail":{"type":1,"value":"-2146232060"}},{"code":"Microsoft.Data.Mashup.ValueError.Message","detail":{"type":1,"value":"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)."}},{"code":"Microsoft.Data.Mashup.ValueError.Number","detail":{"type":1,"value":"131"}},{"code":"Microsoft.Data.Mashup.ValueError.Reason","detail":{"type":1,"value":"DataSource.Error"}}],"exceptionCulprit":1}}} Table: QBR_Policy_ExcessCasualty.
  • Cluster URIWABI-US-EAST2-B-PRIMARY-redirect.analysis.windows.net
  • Activity IDa89224b2-0722-44b1-9523-a264872e268c
  • Request ID98f37760-60ec-d74e-9909-2dfc9287934f
  • Time2023-04-17 15:23:50Z

 

DhananjayN
Regular Visitor

Hi @v-yanjiang-msft ,

 

We are facing the same issue with Synapse tables, while trying to refresh the data in PowerBI, since yesterday.

Below is the error snippet :-

,{"code":"DM_ErrorDetailNameCode_UnderlyingErrorMessage","detail":{"type":1,"value":"Microsoft SQL: The size (8000) given to the convert specification 'nvarchar' exceeds the maximum allowed for any data type (4000)."}},{

 

This worked fine until last weekend. Could you please check and let us know as this is impacting our production refreshes.

Thanks,

DN

chriscooper71
Regular Visitor

@DhananjayN @v-yanjiang-msft I agree with DhanajayN this worked last week fine. Does not work since Saturday 4/15. It's against parquet delta tables for us.

DhananjayN
Regular Visitor

@chriscooper71 @v-yanjiang-msft . Was there any update on the service over the weekend which is causing this? Could you please let us know the next steps?

v-yanjiang-msft
Community Support
Status changed to: Accepted

Hi @chriscooper71 ,

The issue has beed reported internally, please be patient for the fix. If there is any progress, I will update here.

 

Best regards,

Community Support Team_yanjiang

DhananjayN
Regular Visitor

Hi @v-yanjiang-msft , Do we have any update on this?

 

Thanks

v-yanjiang-msft
Community Support

Hi @DhananjayN ,

The status of the issue is Code fix completed, QFE in progress. 

 

Best regards,

Community Support Team_yanjiang

EFI_HSO_TD
New Member

Good morning v-yanjiang-msft,

 

We are still receiving this message - do you have an update on QFE and deployment?

Thanks