Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

String or binary data would be truncated. Expand / Collapse
Author
Message
Posted Thursday, February 14, 2008 4:17 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, May 28, 2009 11:49 PM
Points: 8, 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.
Post #455604
Posted Thursday, February 14, 2008 4:25 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 1:50 AM
Points: 2,397, Visits: 3,408
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"
Post #455606
Posted Thursday, February 14, 2008 4:25 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Wednesday, September 17, 2014 1:05 AM
Points: 2,842, Visits: 3,875
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
Post #455607
Posted Thursday, February 14, 2008 6:09 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, May 28, 2009 11:49 PM
Points: 8, 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

Post #456086
Posted Friday, February 15, 2008 1:40 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, May 28, 2009 11:49 PM
Points: 8, 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.
Post #456188
Posted Friday, February 15, 2008 7:49 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 8:28 AM
Points: 7,064, Visits: 15,280
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?
Post #456305
Posted Friday, February 15, 2008 8:03 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Wednesday, September 10, 2014 8:41 AM
Points: 438, Visits: 908
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.
Post #456316
Posted Friday, February 15, 2008 8:33 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, May 28, 2009 11:49 PM
Points: 8, 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.
Post #456596
Posted Saturday, February 16, 2008 4:07 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Wednesday, September 10, 2014 8:41 AM
Points: 438, Visits: 908
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.
Post #456665
Posted Saturday, February 16, 2008 8:51 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, January 30, 2014 10:08 PM
Points: 1,038, Visits: 444
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.


Post #456681
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse