SQL Server Error:
8152

What does this error mean?

SQL Server throws error 8152 when an INSERT, UPDATE, or MERGE statement tries to write a value that exceeds the defined length of the target column — for example, pushing a 300-character string into a VARCHAR(255). In data pipelines this typically surfaces during ADF Copy Activity or dbt run when source data grows beyond the original column spec. The pipeline activity fails immediately (no partial write), and the entire batch is rolled back. The error message on SQL Server versions before 2019 is notoriously unhelpful: it does not tell you which column or which row caused the problem, making triage slow without trace flag 460 enabled.

Common causes

• 1Source column allows longer values than the target SQL Server column definition — e.g., source is NVARCHAR(MAX) but target is NVARCHAR(255)
• 2ADF Copy Activity auto-generated the sink schema with VARCHAR(50) or NVARCHAR(128) based on a sample, but production data occasionally contains longer strings
• 3A data entry or business logic change in the source system introduced longer values (new product names, longer addresses) without a corresponding ALTER on the target table
• 4dbt model uses a CAST(col AS VARCHAR(100)) or explicit column contract that is narrower than the actual source data
• 5Unicode expansion: source stores ASCII in a VARCHAR column, target uses NVARCHAR — each character takes 2 bytes, effectively halving the usable length for mixed-encoding data

How to fix it

1. 1Step 1: Identify the offending column. On SQL Server 2019+ the error message includes the column name by default. On older versions, enable trace flag 460 globally: DBCC TRACEON(460, -1); — then rerun the failing statement to get the column name and actual length in the error output.
2. 2Step 2: Compare source vs. target lengths. Run on the source: SELECT MAX(LEN(col)) AS max_len, MAX(DATALENGTH(col)) AS max_bytes FROM source_table; Then check the target: SELECT COLUMN_NAME, CHARACTER_MAXIMUM_LENGTH FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'target_table';
3. 3Step 3: Widen the target column if the longer values are legitimate: ALTER TABLE [dbo].[target_table] ALTER COLUMN [col] NVARCHAR(500); For tables with indexes on that column, drop and recreate the index after the ALTER.
4. 4Step 4: If widening is not an option, truncate in the pipeline. In ADF Derived Column: left(col, 255). In dbt: LEFT(col, 255) in your SELECT, or add a pre-hook test with dbt-expectations: expect_column_value_lengths_to_be_between.
5. 5Step 5: For bulk operations, isolate the bad rows first. Insert into a staging table with all columns as NVARCHAR(MAX), then query: SELECT * FROM staging WHERE LEN(col) > 255; — this returns exactly the rows that would fail on insert into the target.
6. 6Step 6: Prevent recurrence. Add a CHECK constraint on the target column: ALTER TABLE [dbo].[target_table] ADD CONSTRAINT CK_col_length CHECK (LEN(col) <= 500); This turns silent truncation risk into an explicit contract.
7. 7Step 7: In ADF, enable Fault Tolerance on the Copy Activity with 'log incompatible rows' to capture truncation failures in a log file without killing the entire pipeline — but treat this as a temporary diagnostic, not a permanent fix.

Example log output

Msg 8152, Level 16, State 14, Line 1
String or binary data would be truncated in table 'analytics.dbo.dim_customer', column 'company_name'. Truncated value: 'Contoso International Holdings Gro'.
The statement has been terminated.

Frequently asked questions

Other data integrity errors