February 14, 2008 at 4:17 am
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.
February 14, 2008 at 4:25 am
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"
February 14, 2008 at 4:25 am
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
February 14, 2008 at 6:09 pm
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
February 15, 2008 at 1:40 am
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.
February 15, 2008 at 7:49 am
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?
February 15, 2008 at 8:03 am
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.
February 15, 2008 at 8:33 pm
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.
February 16, 2008 at 4:07 pm
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.
February 16, 2008 at 8:51 pm
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.
February 22, 2008 at 6:57 am
got you all. will further discuss with my team leader.
Thank you.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply