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.
| Author |
Topic |
|
kwilliams
194 Posts |
 Posted - 2008-08-08 : 10:10:44
|
| I'm trying to convert some of my table's field datatypes from text to varchar within SQL Server 2000 Enterprise Manager, and I was able to do some of them without a problem. But when I tried to finish the last of them, I received this error message:'tblTABLENAME' table- Unable to modify table. ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server]Cannot create a row of size 8810 which is greater than the allowable maximum of 8060.[Microsoft][ODBC SQL Server Driver][SQL Server]The statement has been terminated.I did a count of the characters in the row that I attempted to convert, and the max char count is 2273. I was trying to change it to varchar(5000).Any ideas on how I can get this to work would be GREATLY appreciated. Thanks.KWilliams-------------------It's the end of the world as we know it...and I feel fine |
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2008-08-08 : 11:33:17
|
| Don't confuse the issue.The error is about rowsize not columnsize.Webfred |
 |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-08-09 : 02:41:49
|
| See second point here:-http://support.microsoft.com/kb/260418 |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-08-09 : 02:43:50
|
| Also see thishttp://blogs.msdn.com/msdnts/archive/2006/12/01/row-size-limitation-in-sql-2000-and-2005.aspx |
|
|
|
kwilliams
194 Posts |
Posted - 2008-08-14 : 08:51:33
|
I figured out how to fix it the other day. I simply lowered the length of a few of the larger columns, and it works now.But I must say that webfred's response was quite cold and unhelpful. I'm not sure how he/she their answer would help me in any way. Thank you visakh16 for assisting me...I really appreciate it. KWilliams-------------------It's the end of the world as we know it...and I feel fine |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2008-08-14 : 09:14:20
|
WebFred wrote about thinking FULL RECORD SIZE, which is sum of all columns.Which is what you ultimately did.You can create a table with sum column values greater than 8000 bytes.CREATE TABLE Table1 (i VARCHAR(8000), j VARCHAR(8000), k VARCHAR(8000))You will get an warning (not an error) but the table will still be created.Later when populating the table you will get an error if sum of the actual inserted data exceeds 8060 bytes.But if "i" is 6000 bytes and "j" is 150, the record will be inserted.For another record, if "i" is 10 bytes and "j" is 7900, the record will be inserted.It is the SUM of column size that matters WHEN INSERTING, not the individual column sizes.
E 12°55'05.25"N 56°04'39.16" |
|
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2008-08-14 : 15:25:13
|
quote:
Originally posted by kwilliamsBut I must say that webfred's response was quite cold and unhelpful. I'm not sure how he/she their answer would help me in any way.
Dear kwilliams,i'm sorry for that. It was not my intention to be cold and unhelpful.Next time i will be more detailed in my posts.@Peso: thx for clarification GreetingsWebfredToo Old to Rock 'n' Roll, Too Young to Die |
|
|
|
kwilliams
194 Posts |
Posted - 2008-08-14 : 17:52:36
|
| I appreciate the detailed clarification peso, and your post webfred. I'm sure that I'll be back, but hopefully not too soon.KWilliams-------------------It's the end of the world as we know it...and I feel fine |
|
|
|
|
|
|
|
|
Problem converting datatype text to varchar