Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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

share|improve this question
    
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 Mar 21 '12 at 1:08
5  
Look at the bright side: next time you'll know! –  Remus Rusanu Mar 21 '12 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 Mar 21 '12 at 9:24
add comment

2 Answers

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.

share|improve this answer
add comment

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

share|improve this answer
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 Nov 21 '12 at 14:48
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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