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

Money to Varchar

From query analyzer how can I change the field datatype from money to varchar?
Alter table tablenaame alter column columnname varchar(30)--Is not working.Can you describe "not working" in a bit more detail? Does the command appear to run, but the column datatype doesn't change? Does it give an error message? Does your server crash and spit blue smoke?

-PatP|||You cannot change the datatype to money from (n)varchar, because this conversion cannot be done implicitly. You'll have to create a new table with desired datatype (money), use CONVERT/CAST function to insert the data into it, then drop the original table, create a new table with the same name, and insert the data back in.