ALTER COLUMN on a text or ntext field

Hi,
I'd like to run the following command:
ALTER TABLE cal_respurpose ALTER COLUMN [purpose] varchar(255)
but it falls over because the current [purpose] column is 'text'
I can change it through design view in Enterprise manager, after clicking ok
on the warning message, but I need to find a way to override this error in
Script.
Is there a way I can overide the fact that it is a text column and change it
to varchar?
Thanks in advance!
PaulEXEC sp_rename 'cal_respurpose.purpose', 'purpose_old', 'COLUMN'
ALTER TABLE cal_respurpose ADD purpose VARCHAR(255)
UPDATE cal_respurpose SET purpose = SUBSTRING(purpose_old, 1, 255)
ALTER TABLE cal_respurpose DROP COLUMN purpose_old
"Paul B" <paul.bunting@.archsoftnet.com> wrote in message
news:e0ESFPsvFHA.708@.TK2MSFTNGP10.phx.gbl...
> Hi,
> I'd like to run the following command:
> ALTER TABLE cal_respurpose ALTER COLUMN [purpose] varchar(255)
> but it falls over because the current [purpose] column is 'text'
> I can change it through design view in Enterprise manager, after clicking
> ok on the warning message, but I need to find a way to override this error
> in Script.
> Is there a way I can overide the fact that it is a text column and change
> it to varchar?
> Thanks in advance!
> Paul
>|||When faced with situations like this, it might be helpful for you to
know that you can save the change script (third icon on standard
toolbar) from the Enterprise Manager which will show you how the change
is implemented by the EM. Granted, the method that is implemented is
usually not how I would do it, but it's helpful in a pinch.
In this case, I created and saved a table with a single text column,
and then changed it to a varchar column; this is the script EM used to
implement the change:
BEGIN TRANSACTION
SET QUOTED_IDENTIFIER ON
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
SET ARITHABORT ON
SET NUMERIC_ROUNDABORT OFF
SET CONCAT_NULL_YIELDS_NULL ON
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
COMMIT
BEGIN TRANSACTION
CREATE TABLE dbo.Tmp_test1
(
test varchar(50) NULL
) ON [PRIMARY]
GO
IF EXISTS(SELECT * FROM dbo.test1)
EXEC('INSERT INTO dbo.Tmp_test1 (test)
SELECT CONVERT(varchar(50), test) FROM dbo.test1 TABLOCKX')
GO
DROP TABLE dbo.test1
GO
EXECUTE sp_rename N'dbo.Tmp_test1', N'test1', 'OBJECT'
GO
COMMIT
HTH,
Stu|||Thanks Guys!
"Paul B" <paul.bunting@.archsoftnet.com> wrote in message
news:e0ESFPsvFHA.708@.TK2MSFTNGP10.phx.gbl...
> Hi,
> I'd like to run the following command:
> ALTER TABLE cal_respurpose ALTER COLUMN [purpose] varchar(255)
> but it falls over because the current [purpose] column is 'text'
> I can change it through design view in Enterprise manager, after clicking
> ok on the warning message, but I need to find a way to override this error
> in Script.
> Is there a way I can overide the fact that it is a text column and change
> it to varchar?
> Thanks in advance!
> Paul
>