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
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Error String or binary data would be truncated
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

djcarpen11
Starting Member

25 Posts

Posted - 06/02/2010 :  09:41:52  Show Profile  Reply with Quote
Hi,

My company are in the process of upgrading several SQL 2000 servers to 2008, However we’re getting a very strange error one of our test boxes.

The error is ‘String or binary data would be truncated’ which obviously means that we’re trying to insert a invalid datatype into a record, but when you look into it further the case in our DB is trying to insert a field with a 50nvarchar data type with a max(len) value of 46.

I’ve been able to recreate the error with the below code.

create table test3 (
name varchar (1) ,
tbname varchar (1) 
)


create table maxtable (
tablename varchar (1) not null
)


insert into test3 (name) 
select i.name
from dbo.sysindexes i
inner join  sysobjects o on i.id = o.id
Left Join maxtable m on m.tablename = ''
where i.indid > 0 and i.indid < 255 
and i.name = lower(m.tablename) 

Drop table maxtable
drop table test3



If I run this in SQL 200 or 20505 it runs fine, if I run in 2008 I get the error. Also interestingly if I change the left join to a inner join the statement works in 2008,

Any ideas anyone?

Kristen
Test

United Kingdom
22403 Posts

Posted - 06/02/2010 :  10:53:21  Show Profile  Reply with Quote
If I run this in SQL 2000 it inserts 0 rows.

If I comment out the "and i.name = lower(m.tablename)" then I get the same "data truncated" error (that statement, in there WHERE clause, is in effect changing the OUTER JOIN to an INNER JOIN, no? or if not maybe you have some unusual ANSII settings in SQL 2000?)

Not directly related to this issue, but if you are in the process of migrating this thread may help:

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=138230
Go to Top of Page

djcarpen11
Starting Member

25 Posts

Posted - 06/02/2010 :  11:12:03  Show Profile  Reply with Quote
Thanks for the reply,

The where clause just filters down the result set, so if you remove it you'll get a resultset containing i.name fields that exceed the varchar(1) in the table.

With the where clause included, it creates an empty resultset, so you'd expect to get the message 0 row inserted. Which I do when the code is run in sql 2000, sql 2008 however gives me the 'String or binary data would be truncated' which is strange since there're are no records to insert.
Go to Top of Page

vijayisonly
Flowing Fount of Yak Knowledge

USA
1836 Posts

Posted - 06/02/2010 :  11:15:33  Show Profile  Reply with Quote
Is there a different ANSI_WARNINGS setting between the two.
Go to Top of Page

djcarpen11
Starting Member

25 Posts

Posted - 06/02/2010 :  11:19:32  Show Profile  Reply with Quote
No both the same,

IF I explicitly set ANSI_WARNINGS on I get the same results, Runs in SQL 200 Fails in SQL 2005.
Go to Top of Page

djcarpen11
Starting Member

25 Posts

Posted - 06/02/2010 :  11:22:16  Show Profile  Reply with Quote
soory that should have been fails in sql 2008.
Go to Top of Page

Kristen
Test

United Kingdom
22403 Posts

Posted - 06/02/2010 :  11:28:44  Show Profile  Reply with Quote
" The where clause just filters down the result set, so if you remove it you'll get a resultset containing i.name fields that exceed the varchar(1) in the table."

yeah, but there are NO rows in maxtable so if you OUTER JOIN maxtable and then put WHERE maxtable.AnyColumn = 'XXX' you have changed it to an Inner Join, and because maxtable is empty it won't match any rows ...

... or am I missing something?
Go to Top of Page

djcarpen11
Starting Member

25 Posts

Posted - 06/03/2010 :  04:51:18  Show Profile  Reply with Quote
Yes thats correct, I'm just trying to create sample code that gave the same error as the code that's given us this error.

What I'm struggling to understand if we're returning zero rows to insert in our table, how are we getting the 'String or binary data would be truncated' Error?

The code in my DB brings through data from a col with a varchar(250) data type and inserts it into a varchar(50)(I know the easy and correct way is to match the datatypes). When I run the code that does the insert i get the error, When I've checked the data the max length of of the column I'm trying to insert into the nvarchar(50) column is 45, so in theory that should work? When I look into max length of the data in the column in with varchar(250) it's max lenght is 77.



Go to Top of Page

djcarpen11
Starting Member

25 Posts

Posted - 06/03/2010 :  04:51:38  Show Profile  Reply with Quote
Yes that’s correct, I'm just trying to create sample code that gave the same error as the code that's given us this error.

What I'm struggling to understand if we're returning zero rows to insert in our table, how are we getting the 'String or binary data would be truncated' Error?

I’ll try to expand a little more on the problem I’m having

I have a table that I’m trying to insert data into (TableA) from data in another table (TableB). I’m getting an error inserting into one of the columns saying 'String or binary data would be truncated' . The datatype in destination table(TableA) is Varchar(50) and the source destination is varchar(250) . This is code I’ve inherited as part of an upgrade so obviously changing the destination to match the source would solve my problem.

Although when I take a closer look at the data the max length of the field causing the problem in the only 45 characters long so it should insert in to tableA?

When I take a look at TableB (the source table), the max length of the problem field is 77. If I change the destination table to varchar(78) and run the insert in runs and inserts the data. If I do a select max length on the column I get 45.

It’s as if SQL is working out the max length of the columns in the source table and matching it to the destination table before it’s even run the select? I’ve also checked for trailing spaces etc… so I know the lengths are 100% accurate,

I don’t really want to just match the destination and source data as we’re upgrading several servers and this could potentially be happening 100’s of times.
Go to Top of Page

Kristen
Test

United Kingdom
22403 Posts

Posted - 06/03/2010 :  05:18:16  Show Profile  Reply with Quote
"What I'm struggling to understand if we're returning zero rows to insert in our table, how are we getting the 'String or binary data would be truncated' Error?"

Ah ... sorry, I had not grasped that you were intentionally inserting zero rows
Go to Top of Page

Kristen
Test

United Kingdom
22403 Posts

Posted - 06/03/2010 :  06:10:58  Show Profile  Reply with Quote
... and blow me down you are absolutely right. I tested on SQL2000 & SQL2008

However, this works in SQL2008

insert into test3 (name) 
SELECT a
FROM
(
	SELECT	CONVERT(varchar(10), 'a') AS A
) AS X

and this

create table test4
(
    A varchar (10)
)

INSERT INTO test4 VALUES('a')

insert into test3 (name) 
SELECT a
FROM test4

-- DROP table test4

I tried it using SYSNAME datatype, instead of VARCHAR, and that works fine too.

So, in general, it seems that inserting Large Varchar column into Small Varchar Column, where the Large column actually contains only narrow data, is fine.

The only thing I did see was in the query plan (for your original query) which has:

Table Insert(OBJECT:([test3]), SET:([test3].[name] = [MyDatabase].[sys].[sysidxstats].[name] as [i].[name]))

so the name is actually in sysidxstats table, so I had a look at the sysindexes VIEW and the salient bit is:

SELECT ...
      name
      ...
FROM sys.sysidxstats i 
      OUTER APPLY OpenRowset(TABLE INDEXPROP, id, indid, rowset) p

gawd knows what the effect of the OpenRowset is in there!

Other than that I'm out of ideas ...

Edited by - Kristen on 06/03/2010 06:14:46
Go to Top of Page

djcarpen11
Starting Member

25 Posts

Posted - 06/03/2010 :  06:35:23  Show Profile  Reply with Quote
thanks alot for the reply,

looks like changinging to matching datatypes will be the best fix.
Go to Top of Page

djcarpen11
Starting Member

25 Posts

Posted - 06/03/2010 :  06:39:27  Show Profile  Reply with Quote
one point which is again very strange, if you change left join in my orginal code to a inner join the code works as expected!
Go to Top of Page

Kristen
Test

United Kingdom
22403 Posts

Posted - 06/03/2010 :  07:29:21  Show Profile  Reply with Quote
Well ... the query plans are different, as you might expect, but I can't see anything that suggests this behaviour

Worth checking the Cumulative Updates for SQL2008 to see if there is a fix that cures this problem?
Go to Top of Page

DonAtWork
Flowing Fount of Yak Knowledge

2143 Posts

Posted - 06/03/2010 :  08:23:23  Show Profile  Reply with Quote
I have a table that I’m trying to insert data into (TableA) from data in another table (TableB). 
I’m getting an error inserting into one of the columns saying 'String or binary data would be truncated' . 
The datatype in destination table(TableA) is Varchar(50) and the source destination is varchar(250) .
 This is code I’ve inherited as part of an upgrade so obviously changing the destination to match the source would solve my problem.

Although when I take a closer look at the data the max length of the field causing the problem in the only 45 characters long so it should insert in to tableA?

When I take a look at TableB (the source table), the max length of the problem field is 77. 
If I change the destination table to varchar(78) and run the insert in runs and inserts the data. 
If I do a select max length on the column I get 45.


When I read this, i understand you to say " I have data with a length of 77 characters, and i am trying to cram it into a datatype that will hold 50 characters."

Is this wrong? You said your DESTINATION column is varchar(50). You said your SOURCE DATA has a length of 77. Did i read this wrong?

Regardless, it IS strange to get an error when you insert ZERO rows.

http://weblogs.sqlteam.com/jeffs/archive/2008/05/13/question-needed-not-answer.aspx
How to ask: http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

For ultra basic questions, follow these links.
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp
Go to Top of Page

Kristen
Test

United Kingdom
22403 Posts

Posted - 06/03/2010 :  08:50:17  Show Profile  Reply with Quote
If I understood it correctly:

The target table/column has max 50 characters
The Source has max length of 77 characters, but the longest row's column [being inserted] has 50 characters, or less

Either way ... it should work where zero rows are actually being inserted
Go to Top of Page

RickD
Slow But Sure Yak Herding Master

United Kingdom
3603 Posts

Posted - 06/03/2010 :  09:20:32  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 )?
Go to Top of Page

Kristen
Test

United Kingdom
22403 Posts

Posted - 06/03/2010 :  10:22:46  Show Profile  Reply with Quote
Ah ... I wonder if this is related to "implicit cast issue" between different types?

There were problems with SQL 2000 when SP4 was released on queries comparing disparate types (I think only performance issues, but maybe some queries broke too?, I can't remember ...)

Changing the query from

insert into test3 (name) 
select i.name

to

insert into test3 (name) 
select CONVERT(varchar(1), i.name)

fixes the problem, BUT

insert into test3 (name) 
select CONVERT(varchar(100), i.name)

breaks it again - even though ZERO rows are selected

Using INNNER JOIN with "SELECT CONVERT(varchar(100), i.name)" works OK
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.09 seconds. Powered By: Snitz Forums 2000