String or binary data would be truncated

Question

String or binary data would be truncated

Djcarpen11

Ten Centuries

Points: 1069
More actions
June 3, 2010 at 2:54 am

#223367

I’ll try to expand a little more on the problem I’m having we're upgrading several sql 200 servers to 2008 and come accross the below problem. The code runs fine on sql 2000 but throws a 'String or binary data would be truncated' error on 2008.
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 and also forced the SET ANSI_WARNINGS on.
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.

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Paul White SSC Guru Points: 150467 More actions · Answer 1

I have a feeling you tried quite hard there to make your point clear, but I'm here to tell you that you were not 100% successful 😀

Is it possible to provide a script that replicates the issue? Sometimes a few lines of SQL is worth 2¹⁰ words.

A couple of CREATE TABLE statements, and a row of two of data (INSERT statements) would be great.

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

Djcarpen11 Ten Centuries Points: 1069 More actions · Answer 2

Thanks for the reply,

This code gives me the same result. It's trying to put an empty result set into a table, so you'd expect it to return no rows inserted, insted you get the truncate error.

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

Paul White SSC Guru Points: 150467 More actions · Answer 3

Copy & paste, ran the script, and...

(0 row(s) affected)

No error message. That is on SQL Server 2008 SP1 CU8 build 10.00.2775

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

Djcarpen11 Ten Centuries Points: 1069 More actions · Answer 4

Very strange i'm running it on SQL (SP1) - 10.0.2757.0(x64) -

and also when I run it from (No SP) - 10.0.1600.22 (Intel X86).

I'm a little confused as to why it's running like this. Works fine on SQL 2005 though, very frustrating!

Paul White SSC Guru Points: 150467 More actions · Answer 5

Might be a similar explanation to this Connect Item:

https://connect.microsoft.com/SQLServer/feedback/details/437859/test-performed-on-filtered-out-rows

Optimiser behaviour changes between versions, so it could be that. Without a reproduction script that works for me, there's a limit to the help I can give 🙂

I don't have time for an exhaustive search of the bug fix lists in every patch released since SP1, but that shouldn't stop you, if you are interested. Alternatively, apply the post-SP1 Cumulative Updates **** on a test system *** and see at what stage your problem goes away. You don't have to apply them one-by-one...that's just if you want to fix the problem at the minimum CU level.

Another option: rewrite the query to avoid the apparent compiler issue.

Paul

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

Djcarpen11 Ten Centuries Points: 1069 More actions · Answer 6

Djcarpen11

Ten Centuries

Points: 1069

June 3, 2010 at 5:27 am

#1175832

thanks a lot for your time and help.

Vinay Thankur SSCarpal Tunnel Points: 4178 More actions · Answer 7

what is the output of

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)

this will help us to investigate further.

Thanx.

Vinay

Thanx.
Vinay

http://rdbmsexperts.com/Blogs/
http://vinay-thakur.spaces.live.com/
http://twitter.com/ThakurVinay

Djcarpen11 Ten Centuries Points: 1069 More actions · Answer 8

Djcarpen11

Ten Centuries

Points: 1069

June 3, 2010 at 5:30 am

#1175836

it returns zero rows as expected.