SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 Site Related Forums
 The Yak Corral
 Just in case you missed it ...
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Kristen
Test

United Kingdom
22403 Posts

Posted - 06/03/2010 :  08:54:51  Show Profile  Reply with Quote
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

United Kingdom
3608 Posts

Posted - 06/03/2010 :  09:19:58  Show Profile  Reply with Quote
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 )?

Edited by - RickD on 06/03/2010 09:20:13
Go to Top of Page

Transact Charlie
Flowing Fount of Yak Knowledge

United Kingdom
3451 Posts

Posted - 06/03/2010 :  09:49:13  Show Profile  Visit Transact Charlie's Homepage  Reply with Quote
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
Go to Top of Page

Kristen
Test

United Kingdom
22403 Posts

Posted - 06/03/2010 :  10:28:40  Show Profile  Reply with Quote
Embedded as a derived table gave same result (zero rows selected, but gave "Error String or binary data would be truncated")
Go to Top of Page

Transact Charlie
Flowing Fount of Yak Knowledge

United Kingdom
3451 Posts

Posted - 06/03/2010 :  10:40:37  Show Profile  Visit Transact Charlie's Homepage  Reply with Quote
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

Edited by - Transact Charlie on 06/03/2010 10:40:56
Go to Top of Page

Kristen
Test

United Kingdom
22403 Posts

Posted - 06/03/2010 :  10:52:30  Show Profile  Reply with Quote
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).
Go to Top of Page

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

USA
7020 Posts

Posted - 06/03/2010 :  17:12:18  Show Profile  Reply with Quote
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
Go to Top of Page

Kristen
Test

United Kingdom
22403 Posts

Posted - 06/04/2010 :  02:24:42  Show Profile  Reply with Quote
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
Go to Top of Page

tiffanyyu
Starting Member

4 Posts

Posted - 06/18/2010 :  03:17:32  Show Profile  Visit tiffanyyu's Homepage  Reply with Quote
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?!?).

<spam removed>
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000