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 |
|
magmo
Aged Yak Warrior
558 Posts |
 Posted - 2008-03-26 : 04:04:38
|
| HiI have a column in sql server 2005 that is nVarChar(4000), to be able to copy over thatcolumn and its data to sql server 2000 I had to change the destionation column data type to nText, and when I try to change it back to nVarchar(4000) I get this error "Cannot create a row of size 9317 which is greater than the allowable maximum of 8060." What is wrong?Regards |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2008-03-26 : 04:10:56
|
NVARCHAR(4000) is available in SQL Server 2000, why did you change to NTEXT?Now, somehow, a record has a size of 9317 bytes (NVARCHAR 4658.5) which cannot be converted to NVARCHAR(4000) without loss of information.RunSELECT * FROM Table1 WHERE DATALENGTH(MyNTEXTcol) > 8000to see which record has content greater than 8000 bytes. Then deal with that in some form, ie truncate content.
E 12°55'05.25"N 56°04'39.16" |
 |
|
|
magmo
Aged Yak Warrior
558 Posts |
Posted - 2008-03-26 : 04:19:27
|
| HiI changed it beacuse I got an error while using DTS to copy the data. I also ran your query to find out what row was causing problem, but the weird thing is that I get no hits running that query... Is there something else I can check? |
|
|
|
magmo
Aged Yak Warrior
558 Posts |
Posted - 2008-03-26 : 04:21:47
|
| But when I tryed = 8000, I got 1 hit... |
|
|
|
magmo
Aged Yak Warrior
558 Posts |
Posted - 2008-03-26 : 09:46:19
|
| Hmmm, this is weird... When I removed all text from the column that had "=8000" it worked fine to change it from nText to nVarchar(4000), I could then paste in the extracted text again without any problem..?! |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2008-03-26 : 10:47:42
|
Could be a "limit thingy"Run SELECT * FROM Table1 WHERE DATALENGTH(MyNTEXTcol) > 7990just to be safe.
E 12°55'05.25"N 56°04'39.16" |
|
|
|
PABluesMan
Starting Member
26 Posts |
Posted - 2008-03-26 : 11:07:55
|
| My guess is that the destination table already had other columns totaling 1317 bytes. This plus the 4000 character (8000 byte ... remember, NVARCHAR is Unicode, so there are two bytes per character) pushed things over the limit.I geek, therefore I am |
|
|
|
|
|
|
|
|
max nvarchar size