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
>

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': -
Server: Msg 4928, Level 16, State 1, Line 1
Cannot alter column 'purpose' because it 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
Got answer from Aaron Bertrand [SQL Server MVP] on another newsgroup.
EXEC 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
Cheers!
"Paul B" <paul.bunting@.archsoftnet.com> wrote in message news:%23J%23SJCsvFHA.1996@.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': -
Server: Msg 4928, Level 16, State 1, Line 1
Cannot alter column 'purpose' because it 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
|||Cool, but this code will add the column to the end of the table, the column "purpose" will be the last one in "select * from cal_respurpose". this could be risky if the software or the store procedures performs an insert based on the columns indices.
so what i would suggest is to copy the table to a new table ( with new stucture ) after backing up the table and renaming the new one to the original table name.
I don't know if there is a way to preserve the columns order.
Faris

Quote:

Originally Posted by Paul B

Got answer from Aaron Bertrand [SQL Server MVP] on another newsgroup.
EXEC 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
Cheers!
"Paul B" <paul.bunting@.archsoftnet.com> wrote in message news:%23J%23SJCsvFHA.1996@.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': -
Server: Msg 4928, Level 16, State 1, Line 1
Cannot alter column 'purpose' because it 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

All about Triggers...

Created my first trigger a day or two ago that inserts the current date
into a smalldatetime field WHEN another field is updated. My question:
why is it that one cannot update that field a second time using my
website front end? It's as if you enter in that field's info once and
then you're stuck with it... :/ All the other fields may be modified
at will except that one.
My trigger:
CREATE TRIGGER set_date_trig
ON lla
FOR UPDATE
AS
SET NOCOUNT ON
BEGIN
IF UPDATE(test)
UPDATE lla
SET test_date=GETDATE()
WHERE doc+poe IN (SELECT doc+poe FROM inserted)
END
Any ideas?Try,
...
UPDATE
a
SET
a.test_date=GETDATE()
from
lla as a
inner join
inserted as i
on a.doc = i.doc and a.poe = i.poe
AMB
"roy.anderson@.gmail.com" wrote:

> Created my first trigger a day or two ago that inserts the current date
> into a smalldatetime field WHEN another field is updated. My question:
> why is it that one cannot update that field a second time using my
> website front end? It's as if you enter in that field's info once and
> then you're stuck with it... :/ All the other fields may be modified
> at will except that one.
> My trigger:
> CREATE TRIGGER set_date_trig
> ON lla
> FOR UPDATE
> AS
> SET NOCOUNT ON
> BEGIN
> IF UPDATE(test)
> UPDATE lla
> SET test_date=GETDATE()
> WHERE doc+poe IN (SELECT doc+poe FROM inserted)
> END
>
> Any ideas?
>|||Since this is an UPDATE trigger any change you make to the column Test will
also overwrite Test_date. Maybe you just wanted a DEFAULT value for the
column? Example:
ALTER TABLE lla
ADD CONSTRAINT df_lla_test_date
DEFAULT CURRENT_TIMESTAMP FOR test_date
In your UPDATE:
...
WHERE doc+poe IN
This seems unlikely to be the correct or best way to correlate with the
Inserted table. Is (Doc,Poe) the key? If so:
UPDATE lla SET test_date = CURRENT_TIMESTAMP
WHERE EXISTS
(SELECT *
FROM Inserted AS I
WHERE I.doc = lla.doc
AND I.poe = lla.poe)
Concatenating (or adding!?) the two columns would otherwise not give your
query the full benefit of an index on these columns. Concatenating VARCHAR
columns in this way could also give you incorrect results.
David Portas
SQL Server MVP
--|||The UPDATE() function checks to see if the column is in the update
statement. It does NOT check to see if the value is changing from the
original value. I assume when you try your update statement from the
front-end to set the Test_Date to something expilicit it still sets it to
GetDate(). You may need something like this for your trigger. I'm assuming
your primary key is a combination of Doc and Poe...
CREATE TRIGGER set_date_trig
ON lla
FOR UPDATE
AS
SET NOCOUNT ON
BEGIN
UPDATE lla SET
lla.test_date = GETDATE()
FROM lla
INNER JOIN Inserted I ON lla.Doc = I.Doc AND lla.Poe = I.Poe
INNER JOIN Deleted D ON I.Doc = D.Doc AND I.Poe = D.Poe
WHERE I.Test <> D.Test
END
Paul
"roy.anderson@.gmail.com" wrote:

> Created my first trigger a day or two ago that inserts the current date
> into a smalldatetime field WHEN another field is updated. My question:
> why is it that one cannot update that field a second time using my
> website front end? It's as if you enter in that field's info once and
> then you're stuck with it... :/ All the other fields may be modified
> at will except that one.
> My trigger:
> CREATE TRIGGER set_date_trig
> ON lla
> FOR UPDATE
> AS
> SET NOCOUNT ON
> BEGIN
> IF UPDATE(test)
> UPDATE lla
> SET test_date=GETDATE()
> WHERE doc+poe IN (SELECT doc+poe FROM inserted)
> END
>
> Any ideas?
>