Shortening the length of a column in a table

• Kerstin Safari

  Right there with Babe

  Points: 754

  More actions

  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

• Ninja's_RGR'us

  SSC Guru

  Points: 294069

  More actions

  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)

• Kerstin Safari

  Right there with Babe

  Points: 754

  More actions

  February 13, 2007 at 8:25 pm

  #689622

  Thanks a lot. That worked.

• dbuchan

  SSCrazy

  Points: 2328

  More actions

  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>