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