More on ANSI Padding

The column attribute trimtrailingblanks is changed,
without notice, from yes to no when the length of the
column is altered via the Query Analyzer. Is there a way
to change the trimtrailingblanks property of a column on
the fly?Seems like ALTER TABLE doesn't honor the ANSI_PADDINGS setting and always set this to "no". See
script at end.
AFAIK, there's no way to change this with less that re-create the table.
I don't know if the current behavior is considered a bug or even reported, you might want to check
with MS.
SET ANSI_PADDING ON
GO
create table t(c1 varchar(1) NULL)
GO
EXEC sp_help t
GO
SET ANSI_PADDING OFF
GO
ALTER TABLE t ALTER COLUMN c1 varchar(1) NOT NULL
GO
EXEC sp_help t
GO
DROP TABLE t
GO
SET ANSI_PADDING OFF --Doesn't help
GO
create table t(c1 varchar(1) NULL)
GO
EXEC sp_help t
GO
--SET ANSI_PADDING ON --Doesn't matter
GO
ALTER TABLE t ALTER COLUMN c1 varchar(1) NOT NULL
GO
EXEC sp_help t
GO
DROP TABLE t
GO
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as ugroup=microsoft.public.sqlserver
"Cordelia goh" <cordelia.goh@.gvrd.bc.ca> wrote in message
news:046901c3933d$71b19f20$a101280a@.phx.gbl...
> The column attribute trimtrailingblanks is changed,
> without notice, from yes to no when the length of the
> column is altered via the Query Analyzer. Is there a way
> to change the trimtrailingblanks property of a column on
> the fly?
>sql