SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


String or binary data would be truncated.


String or binary data would be truncated.

Author
Message
SEAH SZE YIN
SEAH SZE YIN
SSC Rookie
SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)

Group: General Forum Members
Points: 38 Visits: 60
My SQL script: -

CREATE TABLE #temp_1
(LEGAL_ENTITY varchar(3) ,
DESCRIPTION varchar(25)
)

DECLARE
@select varchar(2000),
@from varchar(2000),
@where varchar(4000),
@final varchar(8000)

SELECT @select = 'SELECT LEGAL_ENTITY, DESCRIPTION'
SELECT @from = ' FROM table_1 WITH (NOLOCK)'
SELECT @final = @select+@from

INSERT INTO #temp_1

( LEGAL_ENTITY,
DESCRIPTION

)
EXEC (@final)

SELECT LEGAL_ENTITY, DESCRIPTION
From #temp_1

drop table #temp_1

By using above scrript, I will get a error message in SQL2005 database:-
Server: Msg 8152, Level 16, State 14, Line 1
String or binary data would be truncated.

I will not error in SQL2000
or
I comment exec(@final) and directly put in the SELECT statement, th

There is some SELECT statement modification in between that force me must use the variables to construct the SELECT statement. So can anyone advice, how to avoid the error?

Please advice, Thank you.
SwePeso
SwePeso
SSCrazy Eights
SSCrazy Eights (9.3K reputation)SSCrazy Eights (9.3K reputation)SSCrazy Eights (9.3K reputation)SSCrazy Eights (9.3K reputation)SSCrazy Eights (9.3K reputation)SSCrazy Eights (9.3K reputation)SSCrazy Eights (9.3K reputation)SSCrazy Eights (9.3K reputation)

Group: General Forum Members
Points: 9321 Visits: 3433
Already asked and answered here
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=97349


N 56°04'39.16"
E 12°55'05.25"
Christian Buettner-167247
Christian Buettner-167247
SSCertifiable
SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)

Group: General Forum Members
Points: 5205 Visits: 3889
Hello,

could you please let me know the output of the following query?

SELECT MAX(DATALENGTH(LEGAL_ENTITY), MAX(DATALENGTH(DESCRIPTION)
FROM table_1 WITH (NOLOCK)

Thanks!

Best Regards,

Chris Büttner
SEAH SZE YIN
SEAH SZE YIN
SSC Rookie
SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)

Group: General Forum Members
Points: 38 Visits: 60
Thanks for the reply.

I understand that temp table is define as 25, and the actual max length for the data is 40. So the 'Truncate ... ' error return in SQL2005.

However, the same script can be run in SQL2000 without any error. So what happened in the SQL2005? Anyway to bypass the validation in SQL2005?

Please advice. Thank you
SEAH SZE YIN
SEAH SZE YIN
SSC Rookie
SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)

Group: General Forum Members
Points: 38 Visits: 60
Please allow me to further explain my concern here:

The SQL script i posted here is extracted from an existing stored procedure of my system using SQL2000 and it is working fine without return an error. I just upgrade my server to SQL 2005 and error return.

I am thinking that we have so many stored procedure in system, how can i know which stored procedure will works fine in sql2000 but return this kind of error in SQL2005 as well?

So i am looking for a way to bypass this truncate error at database or server level instead of stored procedure.

Please advice. Thanks.
Matt Miller (4)
Matt Miller (4)
One Orange Chip
One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)

Group: General Forum Members
Points: 28609 Visits: 19002
Well - you only have two options: either make the field it's going into bigger (right way), or make what is to go into to it smaller (not the right way). If you really think you need to shorten the input instead of lengthening the output, then use string manipulation functions to pick some part of the field you want. That's LEFT, RIGHT or SUBSTRING, depending on what applies to your scenario.

Unless you know for a fact that there's garbage in the last 15 characters, it seems a strange choice to just truncate. You're screwing up your data.

And since you want to do it at the "database level" and not the SP level - then expanding the field is the solution. I don't know of a "ignore truncation errors" setting, and I don't think 2000 just ignores the error. That certainly doesn't ring true with my past experience.

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
antonio.collins
antonio.collins
SSCrazy
SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)

Group: General Forum Members
Points: 2154 Visits: 921
SEAH SZE YIN (2/15/2008)
Please allow me to further explain my concern here:

The SQL script i posted here is extracted from an existing stored procedure of my system using SQL2000 and it is working fine without return an error. I just upgrade my server to SQL 2005 and error return.

I am thinking that we have so many stored procedure in system, how can i know which stored procedure will works fine in sql2000 but return this kind of error in SQL2005 as well?

So i am looking for a way to bypass this truncate error at database or server level instead of stored procedure.

Please advice. Thanks.


Your SS 2005 db/connection probably has SET ANSI_WARNINGS ON. When this is set, truncation will not be allowed.
from docs:

When set to ON, the divide-by-zero and arithmetic overflow errors cause the statement to be rolled back and an error message is generated. When set to OFF, the divide-by-zero and arithmetic overflow errors cause null values to be returned. The behavior in which a divide-by-zero or arithmetic overflow error causes null values to be returned occurs if an INSERT or UPDATE is tried on a character, Unicode, or binary column in which the length of a new value exceeds the maximum size of the column. If SET ANSI_WARNINGS is ON, the INSERT or UPDATE is canceled as specified by the SQL-92 standard. Trailing blanks are ignored for character columns and trailing nulls are ignored for binary columns. When OFF, data is truncated to the size of the column and the statement succeeds.

Set the option to OFF to mimic the behavior of SS 2000.
SEAH SZE YIN
SEAH SZE YIN
SSC Rookie
SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)

Group: General Forum Members
Points: 38 Visits: 60
Yes, i found the SET ANSI_WARNINGS option might be related to te result i get. So, i try to turn on and off the option and both also give me same error.

A tough task for me to look into all stored procedures for this kind of error.
antonio.collins
antonio.collins
SSCrazy
SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)

Group: General Forum Members
Points: 2154 Visits: 921
remember that if you set ansi_warnings on/off at the db level, you must disconnect/reconnect for the setting to propagate to your session.
Ian Yates
Ian Yates
SSCarpal Tunnel
SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)

Group: General Forum Members
Points: 4488 Visits: 445
Whilst changing the SET options will fix your issue for the time being, you really should always have the ANSI options set because they're required for things such as indexed views, indices on computed columns, etc to be set. Also, some client-side libraries will issue SET options themselves which turn on the ANSI defaults. Fix your table structure or, if you're happy with the truncation, explicitly put the truncation in there using SUBSTRING or LEFT.



Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search