September 10, 2001 at 1:31 pm
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
September 10, 2001 at 3:51 pm
8060 is all you can fit in a row. After that you have to use text or one of the other BLOB types. If you're exceeding this, you should probably be using text anyway!
Was there a problem you were trying to solve by changing the data type?
Andy
September 11, 2001 at 5:42 am
Try capturing what code sql enterprise manager uses to modify the table.
ie run profiler against your test database, change the table via enterprise manager,then review your connection information in the profiler.
regards
Steven
September 12, 2001 at 9:27 am
This is one of our customer - Text datatype can take only 2000 characters they want to increase it to 2048 and thats why need to change text to varchar(2048).
September 12, 2001 at 10:36 am
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply