datatype change from text to varchar

[sonaliak]

i am trying to change the datatype from text to varchar for 1 column and I found that there is no Alter table command to do this so I used following steps to do it:

EXEC sp_rename 'mwebattribmulti.Atmul_comment', 'holdcomm' , 'COLUMN'
go

alter table mwebattribmulti
add Atmul_comment varchar(2048) null
go

update mwebattribmulti
set mwebattribmulti.Atmul_comment = CONVERT(varchar(2048),
SUBSTRING(b.holdcomm, 1, 2048))
from mwebattribmulti, mwebattribmulti b
where mwebattribmulti.atmul_Id= b.atmul_id
go

alter table mwebattribmulti
drop column holdcomm
go

I sent this script to one of the customer, he does not want to use enterprise manager to do this.
But he keeps getting following error:
Server: Msg 4924, Level 16, State 1, Line 3ALTER TABLE DROP COLUMN failed because column 'holdcomm' does not exist intable 'mwebAttribMulti'.Caution: Changing any part of an object name could break scripts and storedprocedures.The COLUMN was renamed to 'holdcomm'.The total row size (8481) for table 'mwebattribmulti' exceeds the maximumnumber of bytes per row (8060). Rows that exceed the maximum number ofbytes will not be added.(10489 row(s) affected)The total row size (8481) for table 'mwebattribmulti' exceeds the maximumnumber of bytes per row (8060). Rows that exceed the maximum number ofbytes will not be added.

What is wrong with my query, I even tries taking out substring but still get the same error. How do I fix this ?
Does any one know what is The total row size for table ?

thanks
Sonali

 

[RickCole]

The max row size in SQL Server 7 is 8060 bytes (not including text fields as these are handled differently) and unfortunately your new table definition is pushing it past this limit.

Rick.