Results 1 to 2 of 2
  1. #1
    Join Date
    Sep 2005
    Posts
    67

    Unanswered: Cannot alter column to make it not null

    I have a table with a TEXT column that allows nulls.
    Now I need to change it, and make it NOT NULL, but I Can't

    ALTER TABLE BUSINESS_UNIT_LANG
    alter column VISION text not null

    And I get:
    Server: Msg 4928, Level 16, State 1, Line 1
    Cannot alter column 'VISION' because it is 'text'.

    Is it impossible to make a text column NOT NULL when it accepts NULLS ?

    All rows and columns are populated, in consequence there isn't any col with null values

    Any idea?
    Thanks!

  2. #2
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    BOL is your freind

    ALTER COLUMN

    Specifies that the given column is to be changed or altered. ALTER COLUMN is not allowed if the compatibility level is 65 or earlier. For more information, see sp_dbcmptlevel.

    The altered column cannot be:

    A column with a text, image, ntext, or timestamp data type.


    The ROWGUIDCOL for the table.


    A computed column or used in a computed column.


    A replicated column.


    Used in an index, unless the column is a varchar, nvarchar, or varbinary data type, the data type is not changed, and the new size is equal to or larger than the old size.


    Used in statistics generated by the CREATE STATISTICS statement. First remove the statistics using the DROP STATISTICS statement. Statistics automatically generated by the query optimizer are automatically dropped by ALTER COLUMN.


    Used in a PRIMARY KEY or [FOREIGN KEY] REFERENCES constraint.


    Used in a CHECK or UNIQUE constraint, except that altering the length of a variable-length column used in a CHECK or UNIQUE constraint is allowed.


    Associated with a default, except that changing the length, precision, or scale of a column is allowed if the data type is not changed.
    Some data type changes may result in a change in the data. For example, changing an nchar or nvarchar column to char or varchar can result in the conversion of extended characters. For more information, see CAST and CONVERT. Reducing the precision and scale of a column may result in data truncation.
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •