String or binary data would be truncated

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

String or binary data would be truncated

Author

Topic

mgm57
Starting Member

8 Posts

Posted - 2010-12-20 : 22:38:50

Hi, I would like to seek help with regards to this issue I am encountering an error in MS SQL 2005 "String or binary data would be truncated." when I run my query below:

SELECT myTableID,
myID,
[month],
CASE
WHEN (LEN(subject_line) > 51) THEN REPLACE(subject_line,subject_line,SUBSTRING(dbo.fnStripHTMLTags(subject_line),0,30)+'...')
ELSE subject_line
END AS subject_line
FROM [dbo].[myTable]
WHERE ([month] = 12
AND myID = 34
AND cType = 1
AND [Year] = '2010')
ORDER BY LastUpdatedDate DESC

When I remove this line of code from the query above, I did not encounter the error:
CASE WHEN (LEN(subject_line) > 51) THEN REPLACE(subject_line,subject_line,SUBSTRING(dbo.fnStripHTMLTags(subject_line),0,30)+'...') ELSE subject_line END AS subject_line

But this line of code is needed in my query. What should I do to fix this? Any help is greatly appreciated.

Thank you.

Sachin.Nand

2937 Posts

Posted - 2010-12-20 : 23:27:48

try increasing the datatype length of the return type for the function fnStripHTMLTags.

PBUH

mgm57
Starting Member

8 Posts

Posted - 2010-12-21 : 00:10:49

For my function fnStripHTMLTags, my return type was already set to "Returns nvarchar(max)" but still issue occurs.

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2010-12-21 : 00:35:19

let's see the definition of the function.

mgm57
Starting Member

8 Posts

Posted - 2010-12-21 : 00:45:52

Here is the full SQL function:

ALTER FUNCTION [dbo].[fnStripHTMLTags]
(
@HTMLData NVARCHAR(MAX)
)
RETURNS NVARCHAR(MAX)
AS

BEGIN
DECLARE @Start int,
@End int,
@Length int
DECLARE @HTMLData2 nvarchar(max)

SELECT @HTMLData2 = SUBSTRING(@HTMLData,CHARINDEX('>',@HTMLData),CHARINDEX('</',@HTMLData)-(CHARINDEX('>',@HTMLData)))

IF (@HTMLData2 != '')
SELECT @HTMLData = SUBSTRING(@HTMLData,CHARINDEX('>',@HTMLData)+1,CHARINDEX('</',@HTMLData)-(CHARINDEX('>',@HTMLData)+1))
WHILE CHARINDEX('<', @HTMLData) > 0 AND CHARINDEX('>', @HTMLData, CHARINDEX('<', @HTMLData)) > 0
BEGIN
SELECT @Start = CHARINDEX('<', @HTMLData),
@End = CHARINDEX('>', @HTMLData, CHARINDEX('<', @HTMLData))
SELECT @Length = (@End - @Start) + 1
IF @Length > 0
BEGIN
SELECT @HTMLData = STUFF(@HTMLData, @Start, @Length, '')
END
END

RETURN @HTMLData
End

Sachin.Nand

2937 Posts

Posted - 2010-12-21 : 01:18:42

Can you post sample data for subject_line which acts as a input for the function ?

PBUH

mgm57
Starting Member

8 Posts

Posted - 2010-12-21 : 01:30:09

Here is the sample subject_line data:
"helo world...kjgksdgsdknvgdknxlnmxckjfdnjklfnjkgfnmd "

mgm57
Starting Member

8 Posts

Posted - 2010-12-21 : 01:46:50

And here is another example data:

" client's place of incarceration have the biggest "

Sachin.Nand

2937 Posts

Posted - 2010-12-21 : 01:58:05

I cannot replicate the error


declare @subject_line varchar(100)='client''s place of incarceration have the biggest '
select CASE 
WHEN (LEN(@subject_line) > 51) THEN REPLACE(@subject_line,@subject_line,SUBSTRING(dbo.fnStripHTMLTags(@subject_line),0,30)+'...')
ELSE @subject_line
END AS subject_line

PBUH

mgm57
Starting Member

8 Posts

Posted - 2010-12-21 : 02:25:29

Thanks PBUH.

Oh my :(, but using the above query together with the other queries from my initial post I am encountering that issue. What could be other possible reason this "String or binary data would be truncated" error is showing?

Sachin.Nand

2937 Posts

Posted - 2010-12-21 : 03:51:47

Actually the error means you are using the length of the data more than its storage area.
Example


declare @t table(val varchar(2))
insert @t
select 'abc'

But remember a variable used in the same way will not cause the error.


declare @var varchar(2)='abc'
select @var

PBUH

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2010-12-21 : 14:52:55

Are you using the query as part of an insert statement?

mgm57
Starting Member

8 Posts

Posted - 2010-12-21 : 20:17:24

Thanks PBUH and russell.

@Russell: No, I am not using this query with Insert Statement. I just use Select Statement for this. The stored procedure was just the one I provided in my first post. Thank you.

ilimax
Posting Yak Master

164 Posts

Posted - 2011-04-01 : 18:47:41

I have same error when I try to insert data ...
1.I have field nvarchar(4000)
2.I measure string inside of .net application ...
if string.lenght < 3998 I insert ...if not I split string ...
I even tried to split to 3000 but error is still cominng up ..."String or binary data would be truncated"

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2011-04-05 : 07:33:56

quote:
Originally posted by Sachin.Nand

Actually the error means you are using the length of the data more than its storage area.
Example

declare @t table(val varchar(2))
insert @t
select 'abc'

But remember a variable used in the same way will not cause the error.

declare @var varchar(2)='abc'
select @var

PBUH

Also refer this
http://beyondrelational.com/blogs/madhivanan/archive/2007/12/04/column-length-and-data-length.aspx

Madhivanan

Failing to plan is Planning to fail

ilimax
Posting Yak Master

164 Posts

Posted - 2011-04-06 : 17:23:18

quote:
Originally posted by madhivanan

quote:
Originally posted by Sachin.Nand

Actually the error means you are using the length of the data more than its storage area.
Example

declare @t table(val varchar(2))
insert @t
select 'abc'

But remember a variable used in the same way will not cause the error.

declare @var varchar(2)='abc'
select @var

PBUH

Also refer this
http://beyondrelational.com/blogs/madhivanan/archive/2007/12/04/column-length-and-data-length.aspx

Madhivanan

Failing to plan is Planning to fail

I am sending 3998 measured text in .net application ... So, my data can not be longer then storage

ilimax
Posting Yak Master

164 Posts

Posted - 2011-04-06 : 17:24:48

Is that mean, .net does not measure length good for SQL Server field ...???

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources