| Author |
Topic |
|
crystal_szeyin
Starting Member
9 Posts |
 Posted - 2008-02-14 : 06:19:15
|
| 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+@fromINSERT INTO #temp_1 ( LEGAL_ENTITY, DESCRIPTION)EXEC (@final) SELECT LEGAL_ENTITY, DESCRIPTIONFrom #temp_1drop table #temp_1 By using above scrript, I will get a error message in SQL2005 database:-Server: Msg 8152, Level 16, State 14, Line 1String or binary data would be truncated.I will not error in SQL2000 or I comment exec(@final) and directly put in the SELECT statement, thThere 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
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2008-02-14 : 06:21:29
|
SELECT @select = 'SELECT left(LEGAL_ENTITY, 3), left(DESCRIPTION, 25)'
E 12°55'05.25"N 56°04'39.16" |
 |
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2008-02-14 : 06:29:22
|
| Also cant you increase the size of the columns of temp table?MadhivananFailing to plan is Planning to fail |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-02-14 : 06:29:57
|
| Thats because some of fields are not having enough length to hold the values inserted to it. Either change the field length to more that MAX(LEN(field))which is inserted or take substring of field value inserted to avoid truncation error using SUBSTRING(),LEFT() etc as Peso suggested. |
|
|
|
crystal_szeyin
Starting Member
9 Posts |
Posted - 2008-02-14 : 20:13:47
|
| 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 |
|
|
|
crystal_szeyin
Starting Member
9 Posts |
Posted - 2008-02-15 : 03:37:13
|
| 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. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-02-15 : 04:40:21
|
| Sorry but i dont think there is any condition which causes this error only in SQL 2005.In both 2000 & 2005, this error will come only when some truncation of strings occurs somewhere. Probably you have an sp which does an insert under some conditions that causes this error which nobody had spotted so far. |
|
|
|
harsh_athalye
Master Smack Fu Yak Hacker
5581 Posts |
Posted - 2008-02-15 : 04:49:50
|
| What is ANSI_PADDING setting for both SQL 2000 and 2005?Harsh AthalyeIndia."The IMPOSSIBLE is often UNTRIED" |
|
|
|
dhurka
Starting Member
2 Posts |
Posted - 2008-05-02 : 16:11:09
|
| Were you able to fix this? I am having the same issue with my app! |
|
|
|
dhurka
Starting Member
2 Posts |
Posted - 2008-05-02 : 16:18:01
|
| FIGURED OUT THE PROBLEM!! need to turn off the ansi_warningsSET ANSI_WARNINGS off |
|
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2008-05-02 : 16:52:43
|
| Well you covered up the error message and avoided the rollback but the data is still being truncated. Just so you know...Be One with the OptimizerTG |
|
|
|
|
Weird: String or binary data would be truncated.