Alter Field Type from nvarchar(255) to nvarchar(Max) in SQL Server 2005

Question

I would like to alter column type in table stk020. So, I used this query..

ALTER TABLE [stk020] 
ALTER COLUMN [t16] nvarchar(Max) not null 

The original column type of [t16] is nvarchar(255). I get this error

Msg 5074, Level 16, State 1, Line 1
The object 'DF_STK020_t16__725CC34D' is dependent on column 't16'.
Msg 4922, Level 16, State 9, Line 1
ALTER TABLE ALTER COLUMN t16 failed because one or more objects access this column.

Any solution?

Answer 1

You must first drop what we presume is the Default constraint on the column before you alter its data type:

Alter Table stk020 Drop Constraint DF_STK020_t16__725CC34D
GO
Alter Table stk020 Alter Column t16 nvarchar(max) not null
GO
Alter Table stk020 Add Constraint DF_STK020_t16__725CC34D
    Default ... For t16