String or binary data would be truncated error

[RDRaider]

How can I find which record(s) cause this error:
Server: Msg 8152, Level 16, State 9, Line 1
String or binary data would be truncated.
The statement has been terminated.

I have tried Profiler but I can't get it to tell me which records are
causing the error.

Here's the script I'm running:
EXEC sp_executesql N'UPDATE IMDISFIL_SQL
SET ITEM_NO = ITEM_NO + ITEM_FILLER WHERE ITEM_FILLER IS NOT NULL'

This table has almost 2 million records so a manual search is not a very
good option.
Thanks in advance.

[JK]

Try this...
DECLARE @length int
SELECT @length = syscolumns.length from syscolumns, sysobjects where
syscolumns.id = sysobjects.id and sysobjects.name = 'IMDISFIL_SQL' and
syscolumns.name = 'ITEM_NO'

SELECT * FROM IMDISFIL_SQL where (LEN(ITEM_NO)+
LEN(ITEM_FILLER))>@length

"RDRaider" <rd******@sbcglobal.net> wrote in message news:<Sh*****************@newssvr14.news.prodigy.c om>...

How can I find which record(s) cause this error:
Server: Msg 8152, Level 16, State 9, Line 1
String or binary data would be truncated.
The statement has been terminated.

I have tried Profiler but I can't get it to tell me which records are
causing the error.

Here's the script I'm running:
EXEC sp_executesql N'UPDATE IMDISFIL_SQL
SET ITEM_NO = ITEM_NO + ITEM_FILLER WHERE ITEM_FILLER IS NOT NULL'

This table has almost 2 million records so a manual search is not a very
good option.
Thanks in advance.

[RDRaider]

Thanks for the help. The query you supplied reports no records that exceed
the length. Could this error also mean there is data in a record that is
invalid for the type? Both Item_No and Item_Filler are type CHAR and length
of 15. I do see some punctuation in some Item_No fields (single and double
quotes and pound sign).
"JK" <ja**************@hotmail.com> wrote in message
news:d5**************************@posting.google.c om...

Try this...
DECLARE @length int
SELECT @length = syscolumns.length from syscolumns, sysobjects where
syscolumns.id = sysobjects.id and sysobjects.name = 'IMDISFIL_SQL' and
syscolumns.name = 'ITEM_NO'

SELECT * FROM IMDISFIL_SQL where (LEN(ITEM_NO)+
LEN(ITEM_FILLER))>@length

"RDRaider" <rd******@sbcglobal.net> wrote in message

news:<Sh*****************@newssvr14.news.prodigy.c om>...

How can I find which record(s) cause this error:
Server: Msg 8152, Level 16, State 9, Line 1
String or binary data would be truncated.
The statement has been terminated.

I have tried Profiler but I can't get it to tell me which records are
causing the error.

Here's the script I'm running:
EXEC sp_executesql N'UPDATE IMDISFIL_SQL
SET ITEM_NO = ITEM_NO + ITEM_FILLER WHERE ITEM_FILLER IS NOT NULL'

This table has almost 2 million records so a manual search is not a very
good option.
Thanks in advance.

[Erland Sommarskog]

RDRaider (rd******@sbcglobal.net) writes:

Thanks for the help. The query you supplied reports no records that
exceed the length. Could this error also mean there is data in a record
that is invalid for the type? Both Item_No and Item_Filler are type
CHAR and length of 15. I do see some punctuation in some Item_No fields
(single and double quotes and pound sign).

> Here's the script I'm running:
> EXEC sp_executesql N'UPDATE IMDISFIL_SQL
> SET ITEM_NO = ITEM_NO + ITEM_FILLER WHERE ITEM_FILLER IS NOT NULL'

First, why sp_executesql? Seems like an overkill here.

Anyway, if ITEM_NO and ITEM_FILLER are char(15), the result is a
char(30) which you cannot cram into an ITEM_NO field. The query:
SELECT * FROM IMDISFIL_SQL where (LEN(ITEM_NO)+
LEN(ITEM_FILLER))>@length

does not give any rows, because len() does not include trailing blanks.

I think this is what you want:

UPDATE IMDISFIL_SQL
SET ITEM_NO = rtrim(ITEM_NO) + rtrim(ITEM_FILLER)
WHERE ITEM_FILLER IS NOT NULL
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp