| Author |
Topic |
|
djcarpen11
Starting Member
25 Posts |
 Posted - 2010-06-02 : 09:41:52
|
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.namefrom dbo.sysindexes iinner join sysobjects o on i.id = o.idLeft Join maxtable m on m.tablename = ''where i.indid > 0 and i.indid < 255 and i.name = lower(m.tablename) Drop table maxtabledrop 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
22859 Posts |
Posted - 2010-06-02 : 10:53:21
|
| 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 |
 |
|
|
djcarpen11
Starting Member
25 Posts |
Posted - 2010-06-02 : 11:12:03
|
| 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. |
|
|
|
vijayisonly
Master Smack Fu Yak Hacker
1836 Posts |
Posted - 2010-06-02 : 11:15:33
|
| Is there a different ANSI_WARNINGS setting between the two. |
|
|
|
djcarpen11
Starting Member
25 Posts |
Posted - 2010-06-02 : 11:19:32
|
| No both the same,IF I explicitly set ANSI_WARNINGS on I get the same results, Runs in SQL 200 Fails in SQL 2005. |
|
|
|
djcarpen11
Starting Member
25 Posts |
Posted - 2010-06-02 : 11:22:16
|
| soory that should have been fails in sql 2008. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-06-02 : 11:28:44
|
| " 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? |
|
|
|
djcarpen11
Starting Member
25 Posts |
Posted - 2010-06-03 : 04:51:18
|
| 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. |
|
|
|
djcarpen11
Starting Member
25 Posts |
Posted - 2010-06-03 : 04:51:38
|
| 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. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-06-03 : 05:18:16
|
"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  |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-06-03 : 06:10:58
|
... and blow me down you are absolutely right. I tested on SQL2000 & SQL2008However, this works in SQL2008insert into test3 (name) SELECT aFROM( SELECT CONVERT(varchar(10), 'a') AS A) AS X and thiscreate table test4( A varchar (10))INSERT INTO test4 VALUES('a')insert into test3 (name) SELECT aFROM test4-- DROP table test4I 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 ... |
|
|
|
djcarpen11
Starting Member
25 Posts |
Posted - 2010-06-03 : 06:35:23
|
| thanks alot for the reply, looks like changinging to matching datatypes will be the best fix. |
|
|
|
djcarpen11
Starting Member
25 Posts |
Posted - 2010-06-03 : 06:39:27
|
| 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! |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-06-03 : 07:29:21
|
| Well ... the query plans are different, as you might expect, but I can't see anything that suggests this behaviourWorth checking the Cumulative Updates for SQL2008 to see if there is a fix that cures this problem? |
|
|
|
DonAtWork
Master Smack Fu Yak Hacker
2167 Posts |
Posted - 2010-06-03 : 08:23:23
|
| [code]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.[/code]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.aspxHow to ask: http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspxFor ultra basic questions, follow these links.http://www.sql-tutorial.net/ http://www.firstsql.com/tutor.htm http://www.w3schools.com/sql/default.asp |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-06-03 : 08:50:17
|
| If I understood it correctly:The target table/column has max 50 charactersThe Source has max length of 77 characters, but the longest row's column [being inserted] has 50 characters, or lessEither way ... it should work where zero rows are actually being inserted |
|
|
|
RickD
Slow But Sure Yak Herding Master
3608 Posts |
Posted - 2010-06-03 : 09:20:32
|
| 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 )? |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-06-03 : 10:22:46
|
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 frominsert into test3 (name) select i.name toinsert into test3 (name) select CONVERT(varchar(1), i.name) fixes the problem, BUTinsert into test3 (name) select CONVERT(varchar(100), i.name) breaks it again - even though ZERO rows are selectedUsing INNNER JOIN with "SELECT CONVERT(varchar(100), i.name)" works OK |
|
|
|
|
Error String or binary data would be truncated