String or binary data would be truncated.

Error Message:
Msg 8152, Level 16, State 14, Line 5
String or binary data would be truncated.

Severity level:
16.

Description:
This error message appears when you try to insert a string with more characters than the column can maximal accommodate.

Consequences:
The T-SQL statement can be parsed, but causes the error at runtime.

Resolution:
Errors of the Severity Level 16 are generated by the user and can be fixed by the SQL Server user. The statement cannot be executed this way. You must either shorten the string to be isnerted to widen the column.

Versions:
All versions of SQL Server.

Example(s):
USE tempdb;
IF OBJECT_ID(‘tempdb.#t’) > 0
 DROP TABLE #t
GO
CREATE TABLE #t
(
 c1 CHAR
);
INSERT INTO #t SELECT ‘abc’
GO

Remarks:
In the above example we try to insert a string ‘abc’ with a length of 3 into the column c1 of the table #t. Because c1 is of the data type CHAR(1), the error is raised.




Related Articles :

  • No Related Articles Found

7 Responses to “String or binary data would be truncated.”

  1. yes .. you are right

  2. Hi,
    How do you figure out which field that causes the error?
    Cheers,
    Mohamed

  3. sir how to fix a [Binary Data] error in microsoft VSTS.

  4. Thank you so much, i hadn’t noticed that i was making a huge mistake, you made my day

  5. No need of changing the column width just use CAST function with required length

  6. Well not always.
    Here’s a statement with the same failure and it turns out that a space after the period is the problem.
    Move the space to the left of the ‘.’ and it is fine.

    ===============
    DECLARE @ag TABLE ( id INT, NAME VARCHAR(20) )
    INSERT INTO @ag
    SELECT id ,
    name
    FROM dbo.AG
    WHERE customerid = 1
    AND name IN (‘Foxborough Reg. Charter’)
    =========================
    This would work.

    WHERE customerid = 1
    AND name IN (‘Foxborough Reg .Charter’)
    ————————-
    Interesting that the ‘.’ is like a delimiter.
    Watch out for this cause it means your query is not correct

  7. thank you sir, i was looking for this solution.i was putting 15 character in a column where i set its datatype varchar(10) only. it worked thanks

Software Reviews | Book Reviews | FAQs | Tips | Articles | Performance Tuning | Audit | BI | Clustering | Developer | Reporting | DBA | ASP.NET Ado | Views tips | | Developer FAQs | Replication Tips | OS Tips | Misc Tips | Index Tuning Tips | Hints Tips | High Availability Tips | Hardware Tips | ETL Tips | Components Tips | Configuration Tips | App Dev Tips | OLAP Tips | Admin Tips | Software Reviews | Error | Clustering FAQs | Performance Tuning FAQs | DBA FAQs |