Error 'String or binary data would be truncated' in Microsoft SQL

Question

I got this error when inserting data to Microsoft SQL.

error: ('22001', '[22001] [Microsoft][ODBC Driver 13 for SQL Server][SQL Server]String or binary data would be truncated. (8152) (SQLParamData); [01000] [Microsoft][ODBC Driver 13 for SQL Server][SQL Server]The statement has been terminated. (3621)')

FYI, I use Python 2.7 and pyodbc library.

What is that error about? What should I do to solve it?

Updated. If you can, please undo the downvote since I already update it. Thanks — Aminah Nuraini, Aug 29 '16 at 07:24
The error message says it all, you try to copy data too big to fit the target, so the data gets truncated. Now you have to find the failing assignment, and adjust it somehow. — jarlh, Aug 29 '16 at 07:26
enlarge your column where you're trying to put a string or binary in that's too long :) — Matthias Burger, Aug 29 '16 at 07:30
and I think the downvote is for "no or too less research". When googling for "String or binary data would be truncated" I get >150k results. So it's okay. — Matthias Burger, Aug 29 '16 at 07:33
Possible duplicate of [SQLException : String or binary data would be truncated](http://stackoverflow.com/questions/779082/sqlexception-string-or-binary-data-would-be-truncated) — Matthias Burger, Aug 29 '16 at 07:35
Yup, but it was nowhere in SO so I put it here and answer myself to make SO complete. Maybe it is considered inappropriate here. — Aminah Nuraini, Aug 29 '16 at 07:35
Maybe my search query was too specific. Should have tried better — Aminah Nuraini, Aug 29 '16 at 07:37

score 26 · Accepted Answer · answered Aug 29 '16 at 07:21

26

Based on this link: http://www.sql-server-performance.com/2007/string-or-binary-data-truncated/

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

answered Aug 29 '16 at 07:21

Aminah Nuraini

18,120
8
90
108

4

I've quoted the essence. I just refer the link to honor the original author – Aminah Nuraini Aug 29 '16 at 08:35

Unnikrishnan R · Answer 2 · 2016-08-29T07:38:43.863

2

figure out the issue using following step. in your insert satement ,use 'LEN' function to find the length of the input columns.

suppose you are selecting data from a temp table for input. use the below query to find out maximum size of that column and compare with the actual size of the column in your original table (table you are going to insert data).

    SELECT MAX(LEN(column1))
    FROM #yourInputData

edited Aug 29 '16 at 07:38

answered Aug 29 '16 at 07:28

Unnikrishnan R

4,965
1
12
21

Error 'String or binary data would be truncated' in Microsoft SQL

2 Answers2