Msg 8152, Sev 16: String or binary data would be truncated. [SQLSTATE 22001]

Question

Msg 8152, Sev 16: String or binary data would be truncated. [SQLSTATE 22001]

Viewing 8 posts - 16 through 22 (of 22 total)

You must be logged in to reply to this topic. Login to reply

e.s.brinkman SSC Journeyman Points: 91 More actions · Answer 1

I had the same problem in Query Analyzer and with the Import Data Wizard. For me the problem was Unicode characters in a text field. After I changed the data type from varchar to nvarchar the problem was gone. Misleading error message from SQL Server. I was looking extensively for the length of the values but in the end it turned out to be a data type issue.

dotnetstudy83 Valued Member Points: 65 More actions · Answer 2

I was also getting same kind of error while inserting into table for removing that error I had increased length of one of the column of table

Scott-144766 SSCarpal Tunnel Points: 4333 More actions · Answer 3

Same issue here. I did notice that my insert query wasn't actually returning any rows, so changed the job from

INSERT INTO Table (Field1, Field2, Field3)

SELECT a.F1, a.F2, a.F3 FROM Table2

to

IF EXISTS (SELECT a.F1, a.F2, a.F3 FROM Table2)

INSERT INTO Table (Field1, Field2, Field3)

SELECT a.F1, a.F2, a.F3 FROM Table2

which did the trick for me.

--
Scott

Rao.V Hall of Fame Points: 3452 More actions · Answer 4

hi.... alll

how i can find the entry which is ofover size than my table's column datatype....

its reallly hard to find out manually as i have more than 60k records in table....

need a way ASAP.....

Ross McMicken SSCarpal Tunnel Points: 4395 More actions · Answer 5

There's no real easy way to find the data that's too long. I usually look at each cloumn from the source and make sure that the receiver columns are appropriately sized. The next step is to do a select max(len(column_name)) from each column to find the longest item in that column. Once yo ufind the column that has long data, then you can use the result to find the bad data. This works for data coming from other sources as well - I've used the technique in Excel a number of times.

Roger Lin-302583 SSC Journeyman Points: 80 More actions · Answer 6

If you don't care the data to be truncated, just turn the ANSI_WARINGS OFF, the data will be truncated to fit the row and no error no fuss everything will work fine. Here is what you can see the effect:

CREATE TABLE #tst (t char(2))

insert #tst values ('abc')

Execute that you get the following error:

Msg 8152, Level 16, State 14, Line 1

String or binary data would be truncated.

The statement has been terminated.

Then run this:

set ansi_warnings off

insert #tst values ('abc')

You will get this:

(1 row(s) affected)

peter.roothans Mr or Mrs. 500 Points: 535 More actions · Answer 7

Hi,

I just had the same issue and it was caused by a trigger that stores all updates in an audit table. The audit table stores some additional information as SUSER_SNAME and APP_NAME ...

After adding substring function the problem was solved

SUBSTRING(APP_NAME(), 1, 50)

APP_NAME() in ssms is about 47 characters but when you execute via a job it returns SQL AGENT .... + a guid ... which is longer than 50 chars.

Kind regards

Peter.

bobaer SSC Enthusiast Points: 101 More actions · Answer 8

Stuart Mark Housden (9/8/2008)
I had this same problem where I always get the "truncation" message when executing stored procedures in SQL Server Agent jobs but never getting the message when I run it in Management Studio.
After trying a number of desperate measures I commented out some "Print" statements I had and the error messages went away.

This was my problem. Thanks!