468,771 Members | 1,545 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 468,771 developers. It's quick & easy.

String or binary data would be truncated error

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.
Jul 20 '05 #1
3 39934
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.

Jul 20 '05 #2
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.

Jul 20 '05 #3
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
Jul 20 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

1 post views Thread by Bernie Yaeger | last post: by
1 post views Thread by CARIGAR | last post: by
1 post views Thread by Marin | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.