Make Column Smaller.... - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Make Column Smaller....

Author

Topic

label
Posting Yak Master

197 Posts

Posted - 2007-12-12 : 09:35:16

I'm trying to make a column smaller by executing this:


-- decrease the description field to 500 characters
if exists (select * from information_schema.columns where table_name = 'registry' and column_name = 'description' and character_maximum_length = 700)
begin
    raiserror('Decrease registry.description field to 500 varchars...', 0, 1) with nowait
    alter table registry alter column "description" varchar(500) not null
    if @@error <> 0 begin rollback transaction return end
end
GO

but I get the following error when I try and execute that statement:


Server: Msg 8152, Level 16, State 9, Line 1
String or binary data would be truncated.
The statement has been terminated.

Obviously I have some data in there that's longer than the 500 char limit I'm setting this to, but I don't care if I lose data. How can I force the reduction of the column length so the statement will run?

Thanks.

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2007-12-12 : 09:48:10

Why would you want to lose data?

AFAIK, you will have to run an update on the table first to update any rows with data longer than 500..

update registry set "description" = substring(1,500,"description")

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-12-12 : 09:48:31

update registry set description = left(coalesce(description, ''), 500)

alter table registry alter column description varchar(500) not null

E 12°55'05.25"
N 56°04'39.16"

label
Posting Yak Master

197 Posts

Posted - 2007-12-12 : 09:54:28

quote:
Originally posted by Peso

update registry set description = left(coalesce(description, ''), 500)

alter table registry alter column description varchar(500) not null

E 12°55'05.25"
N 56°04'39.16"

Thanks to both of you for you quick replies! Losing data can't be avoided based on other changes I'm making to the table. I was hoping to force the alter command to ignore the length of the data in the tables but apparently it's not possible so I'll just run the update to that column with the "left(coalesce(description, ''), 500)" command and it'll be fine.

Thanks again for your help.

X002548
Not Just a Number

15586 Posts

Posted - 2007-12-12 : 14:23:29

Create a new table
Create an insert statement
Rename the old table to table_old
Rename the new table to the old table name

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources