Just in case you missed it ...

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

Site Related Forums

The Yak Corral

Just in case you missed it ...

Author

Topic

Kristen
Test

22859 Posts

Posted - 2010-06-03 : 08:54:51

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=145450

Source table's column varchar(77)
Target table's column varchar(50)

Insert from Source to Target BUT ONLY WHERE source row's column does not exceed 50 characters

Furthermore use criteria such that ZERO rows are selected

Works fine on SQL 2000. On SQL 2008 get "Error String or binary data would be truncated"

Change Outer Join to Inner Join (still ZERO rows selected) and it then works ...

... I'm stumped!

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2010-06-03 : 09:19:58

It seems like the optimiser is trying to get the max length of the varchar fields on either side of the join when using LEFT JOIN (maybe because if it does exist, it will need to be the same length?!?).

If you use a CONVERT or CAST, does that work (yes, I know it won't use an index, but if you wanted that, you should have matched the data properly in the first place

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2010-06-03 : 09:49:13

What happens if you embed the selection from Source into a derived table and then base your insert on that.

Something like:


INSERT target (blah)
SELECT d.(blah)
FROM
    (
    SELECT (blah) AS (blah)
    FROM source
    WHERE LEN(blah) <= 50
    )
    d

Does that still error?

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

Kristen
Test

22859 Posts

Posted - 2010-06-03 : 10:28:40

Embedded as a derived table gave same result (zero rows selected, but gave "Error String or binary data would be truncated")

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2010-06-03 : 10:40:37

Is there any value that has extra space characters after the end?

So where LEN(<column>) is not equal to DATALENGTH(<column>)

Could be that the LEN is including fields that are actually full of spaces?

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

Kristen
Test

22859 Posts

Posted - 2010-06-03 : 10:52:30

Well ... in the example being used the target table's column is actually varchar(1) - so all/many rows would exceed it.

But there is still the issue that ZERO rows are being selected, and ordinarily that isn't an issue - just with this specific query, and then only when using OUTER JOIN (change it to INNER JOIN, which still selects Zero rows, and there is no error).

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2010-06-03 : 17:12:18

I'm am not sure I understand exactly the error you are getting. The following ran with no error for me in SQL 2008


create table #s1 ( x1 varchar(2000) not null )
create table #s1a ( x1 varchar(3000) not null )
create table #s2 ( x2 varchar(50) null, x3 varchar(50) null )
go
-- insert data of random lengths in #s1 column x1
insert into #s1 (x1)
select top 1000
	x1 =  convert(varchar(100),replicate('x',convert(bigint,convert(varbinary(7),newid()))%2001))
from syscolumns
-- insert data of random lengths in #s1a column x1
insert into #s1a (x1)
select top 1000
	x1 =  convert(varchar(100),replicate('x',convert(bigint,convert(varbinary(7),newid()))%2001))
from syscolumns

insert into #s2 (x2,x3)
select
	a.x1,
	b.x1
from
	#s1 a
	full outer join
	#s1a b
	on a.x1 > b.x1
where
	len(a.x1) <= 50

insert into #s2 (x2,x3)
select
	a.x1,
	b.x1
from
	#s1 a
	full outer join
	#s1a b
	on a.x1 > b.x1
where
	1=2

go
drop table #s1
drop table #s1a
drop table #s2

CODO ERGO SUM

Kristen
Test

22859 Posts

Posted - 2010-06-04 : 02:24:42

Sorry guys, I was only meaning to post the link at the top, and a brief summary, in case you were interested - rather than to have the discussion here.

Probably best you see the original thread for the exact query that seems to be "broken" in SQL 2008, that will give you query plans etc. to mull over

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=145450

tiffanyyu
Starting Member

4 Posts

Posted - 2010-06-18 : 03:17:32

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources