Column Resize in SQL 6.5

[scan2k]

Hello,
I'm new to SQL and new to this forum. I've read many user submissions which I've found very helpful. However, I need to know if it is possible to change the length of a column in SQL 6.5. I currently have an Invoice_Line table with approx. 400,000 records. I need to resize the cost_ctr field in one of the rows. It is currently defined as:
cost_ctr: varchar(20) NOT NULL
Any help would be greatly appreciated!!

Thanks,
Scan2k

 

[JohnDTampaBay]

Its easy enough to change the column width in a table:

ALTER TABLE Invoice_Line
ALTER COLUMN cost_ctr varchar(30) NOT NULL

Be advised that this changes the column width for the entire table, not just a specific row. --Angel
-----------------------------------
Every time I lose my mind, I wonder
if it's really worth finding.

 

[scan2k]

Thanks for your reply. This doesn't seem to work in SQL 6.5. Any other suggestions?

 

[JohnDTampaBay]

What exactly is the error message you get? --Angel
-----------------------------------
Every time I lose my mind, I wonder
if it's really worth finding.

 

[scan2k]

Thanks for your response....
The error message I receive is the following:

Msg 156, Level 15, State 1
Incorrect syntax near the keyword 'ALTER'.

I'm using SQL 6.5. The Transact SQL reference manual I have does not show the ALTER COLUMN phrase.

 

[JohnDTampaBay]

Sorry. My bad. Its been a few years since I worked with 6.5. Will Enterprise Manager script the table for you? If so, here's another possible solution:

[li]INSERT the data INTO a temp table[/li]
[li]Script the table definition with permissions, indexes, etc.[/li]
[li]Modify the script with the new column width[/li]
[li]DROP the table[/li]
[li]Execute the modified script to recreate the table[/li]
[li]INSERT the data back into the modified table[/li][/ol]

It seems like there should be an easier way, but it escapes me. --Angel
-----------------------------------
Every time I lose my mind, I wonder
if it's really worth finding.

 

[scan2k]

Thanks for your help with this...I agree, there should be an easier way. I will try this and let you know how it turns out!!

Thanks!!