| Author |
 Topic  |
|
|
pavementsands
Starting Member
3 Posts |
 Posted - 07/04/2012 : 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 - 07/04/2012 : 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 - 07/04/2012 : 10:02:28
|
| Turning off ansi_padding before running doesn't fix it, so maybe not. |
|
|
|
nigelrivett
Flowing Fount of Yak Knowledge
United Kingdom
3328 Posts |
Posted - 07/04/2012 : 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
Flowing Fount of Yak Knowledge
3832 Posts |
Posted - 07/05/2012 : 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?
|
|
|
| |
Topic |
|
Truncation Error where No Truncation Would Occur
