Ansi_Padding - how to get rid of

Hello -
I copied and pasted a database (and log) from Sql Server 2000 to Sql Server
2005 (and attached it).
Everything appeared okay until my VB 6 app had problems with its comboboxes.
I realized that Ansi_Padding was automatically put in by Sql Server 2005 (it
wasn't there in Sql Server 2000), thus making the text in the textbox of the
combo display strangely (e.g. not showing the first few letters of a
particular item, etc).
My question is: How do I get the Ansi_Padding out? It is a varchar field.
Any help will be greatly appreciated!
--
SandySandy (Sandy@.discussions.microsoft.com) writes:
> I copied and pasted a database (and log) from Sql Server 2000 to Sql
> Server 2005 (and attached it).
> Everything appeared okay until my VB 6 app had problems with its
> comboboxes.
> I realized that Ansi_Padding was automatically put in by Sql Server
> 2005 (it wasn't there in Sql Server 2000), thus making the text in the
> textbox of the combo display strangely (e.g. not showing the first few
> letters of a particular item, etc).
> My question is: How do I get the Ansi_Padding out? It is a varchar
> field.
If you attached the database file from SQL 2000, the setting of ANSI_PADDING
should not change, as it saved with the column.
You can verify this by running:
select name, is_ansi_padded
from sys.columns
where object_id('usrdictwords') = object_id
Generally I would recommend that you stick with ANSI_PADDING on, since
there are features that require this setting. In SQL 2000 it was
indexed views and indexed computed columns. In SQL 2005 this require-
ment also applies when you use XQuery.
As for the behaviour of your VB app, it does not sound like ANSI_PADDING
to me. What ANSI_PARDDING is about is what happens to trailing blanks
in varchar when you insert it. With ANSI_PADDING off, they are trimmed,
with ANSI_PADDING on, they are retained.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

ansi padding issues 64 bit vs 32 bit

Hi all.

I'm using the SQL 2005 partitioning schemes to keep 5 weeks worth of data in a table, swapping in a new week and getting rid of the old week. It works fantastic in a 32 bit environment, when I try it in a 64 bit Itanium cluster, I start having issues with the schema's (specifically the ANSI PADDING) being different between my partitioned table and my "Archive" table, the one that I'm switching the old data out to.

When I created the partitioned table originally, I didn't specify ansi padding at all, so my assumption would be that it would take the default of the database, which is "false". However, when you execute a script in Mgt Studio, the default setting is to have ANSI Padding ON. That's fine, so now my tables are all set to ANSI Padding On when I created them, but the database setting is Off, I can live with that. So how come when I run my SSIS Package in a 32 bit environment, which calls 2 stored procedures to do the partitions switching (inside the 2 stored procedures I have dynamic "Create Table" statements for both the New Week and Archive tables, in order to get them on the proper file groups), these Create table statements apparently create the tables with ANSI Padding set to ON as well, because I don't have a problem. But when I use the exact same SSIS Package with the exact same Stored Procedures in the 64 bit environment, I get the following error?

Error: ALTER TABLE SWITCH statement failed because column 'VendorNum' does not have the same ansi trimming semantics in tables 'ODSTJM.dbo.WeeklyActivityCumulative' and 'ODSTJM.dbo.WeeklyActivityCumulativeArchive'.(42000,50000) Procedure(usp_WACPartitionForArchive), Batch 10 Line 188

If I right click either the New or Archive tables (that are dynamically generated) in Mgt Studio and generate script, these 2 tables both Have the ANSI PADDING set to OFF? Every other table is set to ON. Again, this issue doesn't happen in 32 bit, only 64 bit. Any ideas?

Please help.

Andy

This actually ended up being an issue with DBArtisan. By default, dbArtisan has ansi padding set to off, while SQL 2005 has it set to on in Mgt Studio, which was causing the problem. Lesson learned, don't run from dbArtisan.