modifying fields on SQL 6.5

Question

modifying fields on SQL 6.5

eiddie dredd

SSC Rookie

Points: 43
More actions
August 1, 2004 at 9:53 pm

#102138

Hi All,
MSSQL server 6.5
How can i modify a column/field's length or delete existing field in a database table? Bcz it does not allowed me to do that. Is it true that 6.5 version does not allowing alter/drop a fields? I tried altering or dropping by SQLquery with a correct syntax(l), but it still doesnt work..
We also tried to upgrade to SQL 2000, and unfortunately we failed to transfer the exact keys and some other things.... Help please
Kindest Regards,
eiddie dredd
No Pain No Gain

Viewing 6 posts - 1 through 5 (of 5 total)

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

colin.Leversuch-Roberts SSC Guru Points: 52643 More actions · Answer 1

it's a long time since i did 6.5 but I'm pretty sure that you can't alter columns in 6.5 as you can it 2000. I seem to remember I used to script out such changes by creating a new table as a copy and then making a new table etc.

[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/

Eoin The Forge SSCrazy Points: 2872 More actions · Answer 2

You can alter tables (In a Similar Way to EM on SQL2000) using Visual Interdev if you use an DBO login

Martin Cairney Right there with Babe Points: 793 More actions · Answer 3

ALTER TABLE adds columns or constraints to a table, or drops constraints from a table, but it does not allow columns to be removed.

To drop or change a column you will have to create a new table with your required columns and then insert the data from your original table into the new one. You can then drop the original table.

NOTE: if you change e.g. the datatype of a column then SQL Server 2000 effectively does this process anyway - look at the script that you can produce for the change in EM. The difference with SQL Server 2000's ALTER statement is that you can drop a column directly.

eiddie dredd SSC Rookie Points: 43 More actions · Answer 4

Thanks to all of you guys... your info(s) make me happy. Now i get the idea to move from 6.5 to 7 or 2000.

But still sad bcoz i have to replicate and study the "job that done not by me" . The idea is check one by one- keys, stored procedure and any other things that cannot moved and create the new one... what a job... is that worth.. any ideas guys???

Thanks to

Collin;eusullivan;martin

Kindest Regards,

eiddie dredd

No Pain No Gain

colin.Leversuch-Roberts SSC Guru Points: 52643 More actions · Answer 5

I seem to remember that other than straight conversion I scripted out the objects and schema and applied that to a new database first. That picked up any issues as they generated an error on create.

[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/