Data Type Issue in fabric dataware house

[onenessboy]

Hi

I am trying Fabric, tried to load data from my Azure Sql database to Fabric DatawareHouse, Struggling with below error for much time. I read lot of documentation, I tried to create that column with nvarchars(max) then it returned error, the latest sql sever does not support nvarchar(max) where documentation says it allow such defiintion. my data source is Sql server 2022 version, I have this data there without any issue, but when I copy data to ware house, I am getting errors

ErrorCode=UserErrorSqlDWCopyCommandError,'Type=Microsoft.DataTransfer.Common.Shared.HybridDeliveryException,Message=SQL DW Copy Command operation failed with error 'String or binary data would be truncated while reading column of type 'VARCHAR(8000)'. Check ANSI_WARNINGS option. File/External table name

Its really strange Fabric's version sql dataware house denying nvarchar(max) data type ?

Please help

[Poojara_D12]

Hi @onenessboy 

The error you're seeing occurs because SQL Data Warehouse (Synapse) doesn't support nvarchar(max) in the same way as Azure SQL Database.

The solution is to:

Adjust Column Types: Change the column in the destination Data Warehouse to nvarchar(4000) or another fixed size to match the data.

Truncate Data: Use LEFT() to limit the size of the nvarchar(max) data before transferring it.

Disable ANSI_WARNINGS: Try turning off ANSI_WARNINGS in SQL to allow truncation, but this may hide errors.

Check Data Mapping: Ensure source and destination columns are compatible in size and type.

Use Dataflows: Use Fabric Dataflows or Pipelines to preprocess the data if needed.

These steps should help resolve the issue with large data being truncated.

Did I answer your question? Mark my post as a solution, this will help others!

If my response(s) assisted you in any way, don't forget to drop me a "Kudos" 🙂

Kind Regards,
Poojara
Data Analyst | MSBI Developer | Power BI Consultant
Consider Subscribing my YouTube for Beginners/Advance Concepts: https://youtube.com/@biconcepts?si=04iw9SYI2HN80HKS 

[Knuden]

Hi,

Data warehouse in Fabric does not support nvarchar, microsoft suggests to use varchar instead.

[mwsmoley]

Hello onenessboy,

I am not an expert and am new to Fabric and using the data warehouse in fabric as I come from a strong SQL server background. I have run into this issue myself and what you have to understand is that the data warehouse in Fabric is not tradiationl sql server as it is based on parquet files which have different rules. Fabric data warehouse does not have a nvarchar version and it suggests to use varchar instead.  You can review the microsoft documentation at https://learn.microsoft.com/en-us/fabric/data-warehouse/data-types for more details.