November 28, 2011 at 9:29 am
Hello,
Truncation warnings in SSIS are driving me crazy. I went through some online forums where I was able to fix this, But I want to get clear knowledge on this ...
Source table with X column of 30 varchar size, mapped to Destination column of size 20 varchar size.
I get truncation warnings as maaping happens... Fixed this by data conversion transformation Task, setting sorce column size to 20 varchar inside DATA CONVERSION TASK.
Even then I get truncation error. Now Queried on source table with length more than 20 varchar for X column where my data flow transformation fails, Which resulted in few rows {select * from table where len(X)> 20 }. I manually count the number of characters in row for X column, this doesn't go more than 20.
This is kind of supressing me ... I have no idea what its doing. And I have strong feeling this is making my DFT fail while Loding ...Any help is appreciated
November 28, 2011 at 10:23 am
Is it possible that you have spaces which you aren't counting? If SQL Server tells you the length is greater than 20, and when you count the characters you get less than 20, it probably means that you're not counting some characters because you can't see them.
November 28, 2011 at 10:51 am
kramaswamy (11/28/2011)
Is it possible that you have spaces which you aren't counting? If SQL Server tells you the length is greater than 20, and when you count the characters you get less than 20, it probably means that you're not counting some characters because you can't see them.
I am sure there are no spaces. when I Count they are equal to 20 characters, and I DIDN'T GET ON THIS "you're not counting some characters because you can't see them"... can you name any character that we can't see.
My source column is a description field, I don't think there would be any character which can't be see...I totally don't get this ....
November 28, 2011 at 10:59 am
Use DATALENGTH instead.
DECLARE @i VARCHAR(99)
SET @i = '123 ' --- the #'s 1,2 and 3 plus two spaces
SELECT len(@i), datalength(@i)
______________________________________________________________________
Personal Motto: Why push the envelope when you can just open it?
If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.
Jason L. SelburgNovember 28, 2011 at 11:44 am
quillis131 (11/28/2011)
kramaswamy (11/28/2011)
Is it possible that you have spaces which you aren't counting? If SQL Server tells you the length is greater than 20, and when you count the characters you get less than 20, it probably means that you're not counting some characters because you can't see them.I am sure there are no spaces. when I Count they are equal to 20 characters, and I DIDN'T GET ON THIS "you're not counting some characters because you can't see them"... can you name any character that we can't see.
My source column is a description field, I don't think there would be any character which can't be see...I totally don't get this ....
I am not aware of a description data type - is that a varchar(max), nvarchar(max), text or ntext? Either way, you can have hidden control characters in any character data type. That is, you could get {tab} characters - cr/lf, lf only, cr only, nul (char(0)), or a host of other non-printable/viewable characters.
Those characters - although hidden and non-viewable would still count towards the overall length of the column.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
November 28, 2011 at 12:49 pm
Jason Selburg (11/28/2011)
Use DATALENGTH instead.
DECLARE @i VARCHAR(99)
SET @i = '123 ' --- the #'s 1,2 and 3 plus two spaces
SELECT len(@i), datalength(@i)
Thank you ...Its helped me a lot
November 28, 2011 at 12:51 pm
Jeffrey Williams 3188 (11/28/2011)
quillis131 (11/28/2011)
kramaswamy (11/28/2011)
Is it possible that you have spaces which you aren't counting? If SQL Server tells you the length is greater than 20, and when you count the characters you get less than 20, it probably means that you're not counting some characters because you can't see them.I am sure there are no spaces. when I Count they are equal to 20 characters, and I DIDN'T GET ON THIS "you're not counting some characters because you can't see them"... can you name any character that we can't see.
My source column is a description field, I don't think there would be any character which can't be see...I totally don't get this ....
I am not aware of a description data type - is that a varchar(max), nvarchar(max), text or ntext? Either way, you can have hidden control characters in any character data type. That is, you could get {tab} characters - cr/lf, lf only, cr only, nul (char(0)), or a host of other non-printable/viewable characters.
Those characters - although hidden and non-viewable would still count towards the overall length of the column.
I am glad and thankful to your time in writing ... Its clear and detail
November 28, 2011 at 12:59 pm
Jason that's a pretty cool function. I'm curious why it's not used more often? Is there any reason to use LEN instead of DATALENGTH, other than if you are trying to look for a string length and you actually want to have the spaces/tabs/etc be ignored?
Seems to me like people are accustomed to using LEN, and they probably aren't aware that it by default removes non-visible characters. DATALENGTH should be the "default" from what I can tell now.
November 28, 2011 at 1:36 pm
kramaswamy (11/28/2011)
Jason that's a pretty cool function. I'm curious why it's not used more often? Is there any reason to use LEN instead of DATALENGTH, other than if you are trying to look for a string length and you actually want to have the spaces/tabs/etc be ignored?Seems to me like people are accustomed to using LEN, and they probably aren't aware that it by default removes non-visible characters. DATALENGTH should be the "default" from what I can tell now.
To clarify, it doesn't remove any non-visible characters, only the TRAILING SPACES. 😀
DECLARE @i VARCHAR(99)
SELECT @i = '123 ' + char(13) + ' ' --- the #'s 1,2 and 3 + [Carriage Return) + space
SELECT len(@i), datalength(@i)
SELECT @i = '123 ' + char(13) --- the #'s 1,2 and 3 + [Carriage Return)
SELECT len(@i), datalength(@i)
I can only assume why LEN is more commonly used.
First, *most* data doesn't include trailing spaces.
Second, *most* of the time there isn't a need to match/compare with respect to trailing spaces.
Third, (and now I need to research this) it seems trailing spaces are ignored in the WHERE clause regardless of the ANSI_PADDING setting.
The code below (partially stolen from BOL) although lengthly and slightly rough to follow illustrates the question.
PRINT 'Testing with ANSI_PADDING ON'
SET ANSI_PADDING ON;
GO
CREATE TABLE t1 (
charcol CHAR(16) NULL,
varcharcol VARCHAR(16) NULL,
varbinarycol VARBINARY(8)
);
GO
INSERT INTO t1 VALUES ('No blanks', 'No blanks', 0x00ee);
INSERT INTO t1 VALUES ('Trailing blank ', 'Trailing blank ', 0x00ee00);
--- see how the data is stored
SELECT 'CHAR' = '>' + charcol + '<', 'VARCHAR'='>' + varcharcol + '<',
varbinarycol
FROM t1;
GO
-- now use in where clauses
SELECT 'should be nothing - VARCHAR = >No blanks <', 'VARCHAR'='>' + varcharcol + '<'
FROM t1 WHERE varcharcol = 'No blanks '
SELECT 'should be nothing - CHAR = >No blanks <', 'CHAR' = '>' + charcol + '<'
FROM t1 WHERE charcol = 'No blanks '
SELECT 'should be nothing - VARCHAR = >Trailing blank <', 'VARCHAR'='>' + varcharcol + '<'
FROM t1 WHERE varcharcol = 'Trailing blank'
SELECT 'should be nothing - CHAR = >Trailing blank <', 'CHAR' = '>' + charcol + '<'
FROM t1 WHERE charcol = 'Trailing blank'
GO
PRINT 'Testing with ANSI_PADDING OFF';
SET ANSI_PADDING OFF;
GO
CREATE TABLE t2 (
charcol CHAR(16) NULL,
varcharcol VARCHAR(16) NULL,
varbinarycol VARBINARY(8)
);
GO
INSERT INTO t2 VALUES ('No blanks', 'No blanks', 0x00ee);
INSERT INTO t2 VALUES ('Trailing blank ', 'Trailing blank ', 0x00ee00);
--- see how the data is stored
SELECT 'CHAR' = '>' + charcol + '<', 'VARCHAR'='>' + varcharcol + '<',
varbinarycol
FROM t2;
-- now use in where clauses
SELECT 'should be nothing - VARCHAR = >No blanks <', 'VARCHAR'='>' + varcharcol + '<'
FROM t2 WHERE varcharcol = 'No blanks '
SELECT 'should be nothing - CHAR = >No blanks <', 'CHAR' = '>' + charcol + '<'
FROM t2 WHERE charcol = 'No blanks '
SELECT 'should be nothing - VARCHAR = >Trailing blank <', 'VARCHAR'='>' + varcharcol + '<'
FROM t2 WHERE varcharcol = 'Trailing blank'
SELECT 'should be nothing - CHAR = >Trailing blank <', 'CHAR' = '>' + charcol + '<'
FROM t2 WHERE charcol = 'Trailing blank'
GO
DROP TABLE t1
DROP TABLE t2
______________________________________________________________________
Personal Motto: Why push the envelope when you can just open it?
If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.
Jason L. SelburgNovember 28, 2011 at 3:36 pm
I think LEN is a holdover from the other languages where that's a common function. Not sure when DATALENGTH was introduced (it's in 2000), but it might also be legacy code and examples from the v6.5 or v7 days.
November 28, 2011 at 4:00 pm
Steve Jones - SSC Editor (11/28/2011)
I think LEN is a holdover from the other languages where that's a common function. Not sure when DATALENGTH was introduced (it's in 2000), but it might also be legacy code and examples from the v6.5 or v7 days.
I don't know when it was introduced either - but you need to be aware of the differences between the two. For char and varchar data types, datalength will return the same and len - but for nchar and nvarchar it will be the actual data length and LEN will return the number of characters.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply