alter text column

Hi

I had a text type not null column which i wanted to change to a null column.Writing a simple alter statement gave me an eror cannot change text type column so i tried to rename the original column create a new column with the same name and allowing nulls on it and then copying the contents of the renamed column to the new column and finally deleting the renamed column.

EXEC sp_rename 'TableName.ColumnName', 'ColumnName_old', 'COLUMN'

ALTER TABLE TableName ADD ColumnName text NULL
UPDATE TableName SET ColumnName = ColumnName_old
ALTER TABLE TableName DROP COLUMN ColumnName_old

However when i tried to execute these statements in query analyser on the Update statement it gave me the error that ColumnName_old does not exist.

However then I tried to execute these queries one by one I was able to do that.

Can anybody tell me whats causing the queries to not be executed all at once without giving the ColumnName_old does not exist error cause I wanted to run them on live dbs.

any help would be appreciated.

Himani

Your logic should work with a little change. Add a GO between each batch.

Like:

EXEC sp_rename 'TableName.ColumnName', 'ColumnName_old', 'COLUMN'

GO

ALTER TABLE TableName ADD ColumnName text NULL

GO
UPDATE TableName SET ColumnName = ColumnName_old

GO
ALTER TABLE TableName DROP COLUMN ColumnName_old

GO

After each DML statement executed, you should get what you want.

By the way, it seems you can change the colum with text datatype from not null to allow null directly from the table definition in SQL 2005 Management Studio.

Also, you can directly do the update like this:

UPDATE yourTable

Set yourNewcolumTextAllowNull=youroldcolumnnotAllowNull

HTH

|||

Thanks a lot limno,it worked !!!

:-)

sql