BTW, I think the error in the syntax is that NOT NULL part should be the last. I didn't try now, but I'm 99% certain I'm right.
>>>What Antonio suggested works. Except when I use the default.
>>>
>>>Example:
>>>
>>>ALTER TABLE MyTable ALTER COLUMN notes varchar(max) not null default ''
>>>
>>>
>>>I am getting error "near key word Default"
>>>
>>>What am I missing?
>>>
>>>TIA
>>
>>Dmitry, I think this is how MSSQL adds a default value to an existing column:
>>
>>
>>ALTER TABLE MyTable ADD CONSTRAINT DF_NotesAreEmpty DEFAULT '' FOR notes;
>>
>
>Antonio,
>I am sure your code works. But what I found is that if I change the type from Text to Varchar(max) and simply add NOT NULL, the default of the Text type stays. So, in the end I don't need to set the default.
>Thank you.
>
>UPDATE. I believe you are right. I have to "deal" with the constraint. Because when I ALTER the column, the SQL complaints that the field NOTES depends on the constraint. So, I will have to drop the constraint first.
>It is never very simple :)
If it's not broken, fix it until it is.
My Blog