Re: How to alter a Varchar column to a shorter length (with data in it)

From: Geoff N. Hiten (SRDBA_at_Careerbuilder.com)
Date: 06/28/04 

Date: Mon, 28 Jun 2004 14:36:51 -0400

Update table Unit
    set Unit_Name = left(Unit_Name, 15)
    where len(Unit_Name) > 15

Then run your alter table command. 

-- 
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"C. Ming Lu" <cminglu@qmxs.com> wrote in message
news:OA8na3TXEHA.2844@TK2MSFTNGP12.phx.gbl...
> Hi,
>
> I am trying to write a script to trim the length of a paticular column
> 'Unit_Name' (its orginial length is: 256). There are already data in the
> table (with length over 15 chararters).
>
> The following script gives me a erro and aborted.
>     Alter table Unit alter column Unit_Name varchar (15) null
>     go
>
> Question:
>    The data can be truncated, how do I suppress the error and force the
> script finish ?
>
> Thanks.
>
> --- Charlie
>
>

Relevant Pages

  • Re: ALTER IDENTITY setting...
    ... Enterprise Manager would do. ... I also would suggest you use EM and save the script. ... Pro SQL Server 2000 Database Design - ... > I just need to alter column with Identity. ...
    (microsoft.public.sqlserver.programming)
  • Re: automate tool to change column type?
    ... schema ahead of time. ... is the change such that you can do it with ALTER TABLE? ... but save and carefully review the script. ... Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx ...
    (microsoft.public.sqlserver.tools)
  • Re: changing collations
    ... ALTER TABLE ALTER ... > am looking for an automated tool or some script which does it. ... It could be worth the investment to look into using DMO for the task. ... Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx ...
    (comp.databases.ms-sqlserver)
  • Re: Problem altering table and adding a default
    ... ALTER TABLE dbo.Test_tbl ADD Notnull_col1 charNOT NULL ... SQL Server does not allow that - you can only add nullable columns. ... After you add a new column in Enterprise Manager, uncheck Allow Nulls, bind the uder- ... Start Profiler and perform a new trace. ...
    (microsoft.public.sqlserver.server)
  • Re: Problem altering table and adding a default
    ... SQL Server does not allow that - you can only add nullable ... > After you add a new column in Enterprise Manager, uncheck Allow Nulls, ... the ALTER TABLE statement above will pass to the SQL Server ... > 4) Start Profiler and perform a new trace. ...
    (microsoft.public.sqlserver.server)
Quantcast