Post reply

Shortening the length of a column in a table

  • February 13, 2007 at 5:18 pm

    #62350

    I need to make a column in a table char(200) instead of char(300). I think the syntax for altering a column is pretty straightforward, but what I am running into is a data truncation problem. The user doesn't care if the data is truncated or not, but MSSQL does, and it won't continue if it thinks that it might truncate something.

    How do I get around this?

    Thanks

  • February 13, 2007 at 6:57 pm

    #689613

    UPDATE dbo.YourTable SET Colname = LEFT(ColName, 200)

    ALTER TABLE dbo.YourTable

    ALTER COLUMN ColName CHAR(200)

  • February 13, 2007 at 8:25 pm

    #689622

    Thanks a lot. That worked.

  • February 14, 2007 at 8:53 pm

    #689865

    or on a per-session basis-

    SET ANSI_WARNINGS

    Specifies SQL-92 standard behavior for several error conditions.

    Syntax

    SET ANSI_WARNINGS { ON | OFF }

    Remarks

    SET ANSI_WARNINGS affects these conditions:

    • When ON, if null values appear in aggregate functions (such as SUM, AVG, MAX, MIN, STDEV, STDEVP, VAR, VARP, or COUNT) a warning message is generated. When OFF, no warning is issued.
    • When ON, divide-by-zero and arithmetic overflow errors cause the statement to be rolled back and an error message is generated. When OFF, divide-by-zero and arithmetic overflow errors cause null values to be returned. The behavior in which a divide-by-zero or arithmetic overflow error causes null values to be returned occurs if an INSERT or UPDATE is attempted on a character, Unicode, or binary column in which the length of a new value exceeds the maximum size of the column. If SET ANSI_WARNINGS is ON, the INSERT or UPDATE is canceled as specified by the SQL-92 standard. Trailing blanks are ignored for character columns and trailing nulls are ignored for binary columns. When OFF, data is truncated to the size of the column and the statement succeeds.

     


    The systems fine with no users loggged in. Can we keep it that way ?br>

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply