ALTER TABLE SWITCH statement failed

ALTER TABLE SWITCH statement failed because column does not have the same ANSI trimming semantics in tables...

Does anyone knows how to fix that on already existing table full of data?

Well, I've found that it is simply possible to recreate same table with same ANSI_PADDING parameter, but the problem is that the table I'm trying to switch into a larger main table is already filled with data. I don't want to spend again 37 hours to reload that partition =(

I hate to say this, but there's a bug in Books Online. The Books Online page on partition switching says:


Source and target tables must have the same column structure and
order. The tables must have the same columns with the same names and
the same data type, length, collation, precision, scale, nullability,
and PRIMARY KEY constraints (if any), and also have the same settings
for ANSI_NULLS and QUOTED IDENTIFIER.

But as you've discovered ANSI_PADDING is also involved - both the incoming & outgoing tables have to have the same setting. There is no way to fix this after the data is loaded.

StackExchange Database Administrators Q#15325, answer score: 3