Truncation Error where No Truncation Would Occur

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

SQL Server 2005 Forums

Transact-SQL (2005)

Truncation Error where No Truncation Would Occur

Author

Topic

pavementsands
Starting Member

3 Posts

Posted - 2012-07-04 : 07:46:55

Hi All,
This is baffling us - we've had it in two separate cases now.
We get the usual dreaded "String or binary data would be truncated." error when tying to insert into table tbl_xxx

So I go through the usual process of running the same statement rewritten as select x,y,z into #temp from tbl_source
instead, and then I check the data lengths in the temp table.

But in this case, nothing is over long - no values are longer than the field lengths in tbl_xxx.

Even weirder, just to check this out I ran an insert into statement from the temp table into the original destination table tbl_xxx and there was no truncation error. The error only occurs when trying to insert directly into the table. The same data can go via a temp table with no problem

Any idea what's going on here?

pavementsands
Starting Member

3 Posts

Posted - 2012-07-04 : 09:52:08

quick thought:
To recap the problem because it's not very clear above

This statement gives truncation error
-------------------------
--insert into, direct into final table in dest database

Insert into DestinationDatabase.dbo.tbl_x
Select x,y,z
from SourceDatabase.dbo.tbl_a
-------------------------

Doing it this way however, doesn't give a truncation error:
----------------------------------------------------
-- select into temporary table in dest database
Select x,y,z
into DestinationDatabase.dbo.tbl_temp_x
from SourceDatabase.dbo.tbl_a

--insert into final table in dest database
insert into DestinationDatabase.dbo.tbl_x
select * from DestinationDatabase.dbo.tbl_temp_x
----------------------------------------------------

However, I've just spotted that Ansi padding is false in destination database, true in source database. Could this be the problem?

Ta
Dan

pavementsands
Starting Member

3 Posts

Posted - 2012-07-04 : 10:02:28

Turning off ansi_padding before running doesn't fix it, so maybe not.

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2012-07-04 : 11:02:42

Maybe a collation issue? What datatypes are the columns?

try
select x,y,z into #a from DestinationDatabase.dbo.tbl_x where 1=0
insert #a select * x,y,z from SourceDatabase.dbo.tbl_a
select x,y,z into #b from SourceDatabase.dbo.tbl_a where 1=0
insert #b select * x,y,z from SourceDatabase.dbo.tbl_a

If that doesn't help - use a where clause for the insert and do a binary chop to find a single row then a single column that causes the issue. Actually might be better to find the column first.
Will probably be clear what the problem is then.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2012-07-05 : 12:02:42

What are the datatypes of the columns you are using on the two tables: DestinationDatabase.dbo.tbl_x and SourceDatabase.dbo.tbl_a?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources