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)

[v-yanjiang-msft]

Hi @chriscooper71 ,

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

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

Best regards,

Community Support Team_yanjiang

[chriscooper71]

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]

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]

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]

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

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

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]

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

Thanks

[v-yanjiang-msft]

Hi @DhananjayN ,

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

Best regards,

Community Support Team_yanjiang

[EFI_HSO_TD]

Good morning v-yanjiang-msft,

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

Thanks