5

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 =(

Improve this question
3
  • The problematic column has different setting "ansi padding status" on source and target tables. Obviously because I didn't pay attention to it when was creating staging table. Is there way to alter this property?
    – Vlad Ogay
    Commented Mar 21, 2012 at 1:08
  • 5
    Look at the bright side: next time you'll know!
    – Remus Rusanu
    Commented Mar 21, 2012 at 6:04
  • 1
    I like your optimism)) For now it appeared that there two more tables with the same problem so overall reload time will become ~50h.
    – Vlad Ogay
    Commented Mar 21, 2012 at 9:24

2 Answers 2

Reset to default
3

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.

Improve this answer
0
-2

create the create table script of source table , modify the create table script as to create a new target table then run the switch command again

Improve this answer
1
  • 2
    -1 this doesn't address "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"
    – Jack Douglas
    Commented Nov 21, 2012 at 14:48

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.