February 8, 2009 at 10:37 am
I've been trying to go though and cleanup our database and start to remove all (n)text columns and convert them to nvarchar(max).
so far it's worked pretty well but i've hit a snag with some larger table that contain 2-300+ columns. When converting the columns on these tables about half way though i'll get this dreaded error.
Msg 511, Level 16, State 1, Line 1
Cannot create a row of size 8068 which is greater than the allowable maximum of 8060.
The statement has been terminated.
I'm a bit lost on what to do since i thought the varchar(max) was pretty much a direct replacement.
any help is appriciated in how to get the rest of the columns converted.
thank you!
February 8, 2009 at 10:43 am
8060 is still the limit for fixed size columns in a row.
Which version of SQL2005 are you on ?
Select @@version
Can you post the original table ddl ?
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
February 8, 2009 at 12:29 pm
Here is our sql server version.
Microsoft SQL Server 2005 - 9.00.3239.00 (X64) Apr 9 2008 22:41:28 Copyright (c) 1988-2005 Microsoft Corporation Enterprise Edition (64-bit) on Windows NT 5.2 (Build 3790: Service Pack 2)
Here is the original table create statement. as you can see there are a lot of columns and a lot of them are text. We have hundreds of tables like this in our system so i'm a bit flustered if I'm going to run into this issue on all of them.
CREATE TABLE [dbo].[GFS_A_0104](
[JobID] [int] NULL,
[ShopTimeT] [datetime] NULL,
[CallTimeDur] [float] NULL,
[ShopTime] [datetime] NULL,
[VisitTimeDur] [float] NULL,
[Weather_Conditions] [text] NULL,
[SubjectShop_Description] [text] NULL,
[SubjectShop_Description_Gender] [text] NULL,
[SubjectShop_Description_Hair] [text] NULL,
[SubjectShop_Description_Hair_Color] [text] NULL,
[SubjectShop_Description_Height] [text] NULL,
[SubjectShop_CO] [text] NULL,
[SubjectShop_CO_Description] [text] NULL,
[SubjectShop_CO_Description_Gender] [text] NULL,
[SubjectShop_CO_Description_Hair] [text] NULL,
[SubjectShop_CO_Description_Hair_Color] [text] NULL,
[SubjectShop_CO_Description_Height] [text] NULL,
[Num_Carts_Begin] [int] NULL,
[Num_Carts_End] [int] NULL,
[Item_Purchased] [text] NULL,
[Item_Description] [text] NULL,
[Cash_Expense] [float] NULL,
[Expense] [float] NULL,
[Phone_Call_1] [text] NULL,
[Phone_Call_2_a] [tinyint] NULL,
[Phone_Call_2_b] [tinyint] NULL,
[Phone_Call_2_c] [tinyint] NULL,
[Phone_Call_2_c_a] [text] NULL,
[Phone_Call_2_d] [tinyint] NULL,
[Phone_Call_2_e] [tinyint] NULL,
[Phone_Call_3] [tinyint] NULL,
[Phone_Call_4] [text] NULL,
[Phone_Call_5] [tinyint] NULL,
[Phone_Call_6_a] [text] NULL,
[Phone_Call_6_b] [text] NULL,
[Phone_Call_6_c] [tinyint] NULL,
[Phone_Call_6_c_a] [tinyint] NULL,
[Phone_Call_6_c_b] [text] NULL,
[Phone_Call_6_d] [tinyint] NULL,
[Phone_Call_6_e] [tinyint] NULL,
[Phone_Call_7] [tinyint] NULL,
[Phone_Call_7_a] [text] NULL,
[Phone_Call_Comments] [ntext] NULL,
[Store_Exterior_Ex] [tinyint] NULL,
[Store_Exterior_Ex_a] [bit] NULL,
[Store_Exterior_Ex_b] [bit] NULL,
[Store_Exterior_Ex_c] [bit] NULL,
[Store_Exterior_Ex_d] [bit] NULL,
[Store_Exterior_Ex_e] [bit] NULL,
[Store_Exterior_Ex_Other] [text] NULL,
[Store_Exterior_1] [tinyint] NULL,
[Store_Exterior_1_a] [bit] NULL,
[Store_Exterior_1_b] [bit] NULL,
[Store_Exterior_1_c] [bit] NULL,
[Store_Exterior_1_d] [bit] NULL,
[Store_Exterior_1_e] [bit] NULL,
[Store_Exterior_1_Other] [text] NULL,
[Store_Exterior_2] [tinyint] NULL,
[Store_Exterior_3] [tinyint] NULL,
[Store_Exterior_3_a] [bit] NULL,
[Store_Exterior_3_b] [bit] NULL,
[Store_Exterior_3_c] [bit] NULL,
[Store_Exterior_3_d] [bit] NULL,
[Store_Exterior_3_Other] [text] NULL,
[Store_Exterior_4] [tinyint] NULL,
[Store_Exterior_4_a] [bit] NULL,
[Store_Exterior_4_b] [bit] NULL,
[Store_Exterior_4_c] [bit] NULL,
[Store_Exterior_4_d] [bit] NULL,
[Store_Exterior_4_e] [bit] NULL,
[Store_Exterior_4_Other] [text] NULL,
[Store_Exterior_5] [tinyint] NULL,
[Store_Exterior_5_a] [bit] NULL,
[Store_Exterior_5_b] [bit] NULL,
[Store_Exterior_5_c] [bit] NULL,
[Store_Exterior_5_d] [bit] NULL,
[Store_Exterior_5_e] [bit] NULL,
[Store_Exterior_5_f] [bit] NULL,
[Store_Exterior_5_g] [bit] NULL,
[Store_Exterior_5_Other] [text] NULL,
[Store_Interior_1_a] [tinyint] NULL,
[Store_Interior_1_a_1] [text] NULL,
[Store_Interior_1_b] [tinyint] NULL,
[Store_Interior_1_b_1] [text] NULL,
[Store_Interior_1_c] [tinyint] NULL,
[Store_Interior_1_c_1] [text] NULL,
[Store_Interior_1_d] [tinyint] NULL,
[Store_Interior_1_d_1] [text] NULL,
[Store_Interior_2] [tinyint] NULL,
[Store_Interior_2_a] [bit] NULL,
[Store_Interior_2_b] [bit] NULL,
[Store_Interior_2_c] [bit] NULL,
[Store_Interior_2_Other] [text] NULL,
[Store_Interior_2_Time] [datetime] NULL,
[Store_Interior_2_Aisle] [text] NULL,
[Store_Interior_3] [tinyint] NULL,
[Store_Interior_3_a] [text] NULL,
[Store_Interior_4] [tinyint] NULL,
[Store_Interior_4_a] [text] NULL,
[Merch_Pres_1_1_Organized] [tinyint] NULL,
[Merch_Pres_1_1_Faced] [tinyint] NULL,
[Merch_Pres_1_1_Stocked] [tinyint] NULL,
[Merch_Pres_1_1_Explain] [text] NULL,
[Merch_Pres_1_2_Organized] [tinyint] NULL,
[Merch_Pres_1_2_Faced] [tinyint] NULL,
[Merch_Pres_1_2_Stocked] [tinyint] NULL,
[Merch_Pres_1_2_Explain] [text] NULL,
[Merch_Pres_1_3_Organized] [tinyint] NULL,
[Merch_Pres_1_3_Faced] [tinyint] NULL,
[Merch_Pres_1_3_Stocked] [tinyint] NULL,
[Merch_Pres_1_3_Explain] [text] NULL,
[Merch_Pres_1_4_Organized] [tinyint] NULL,
[Merch_Pres_1_4_Faced] [tinyint] NULL,
[Merch_Pres_1_4_Stocked] [tinyint] NULL,
[Merch_Pres_1_4_Explain] [text] NULL,
[Merch_Pres_2_1] [text] NULL,
[Merch_Pres_2_1_Shelf] [tinyint] NULL,
[Merch_Pres_2_1_Stock] [tinyint] NULL,
[Merch_Pres_2_2] [text] NULL,
[Merch_Pres_2_2_Shelf] [tinyint] NULL,
[Merch_Pres_2_2_Stock] [tinyint] NULL,
[Merch_Pres_2_3] [text] NULL,
[Merch_Pres_2_3_Shelf] [tinyint] NULL,
[Merch_Pres_2_3_Stock] [tinyint] NULL,
[Merch_Pres_2_4] [text] NULL,
[Merch_Pres_2_4_Shelf] [tinyint] NULL,
[Merch_Pres_2_4_Stock] [tinyint] NULL,
[Merch_Pres_2_5] [text] NULL,
[Merch_Pres_2_5_Shelf] [tinyint] NULL,
[Merch_Pres_2_5_Stock] [tinyint] NULL,
[Merch_Pres_3] [tinyint] NULL,
[Merch_Pres_Comments] [ntext] NULL,
[Gen_Service_Observation_Cust] [int] NULL,
[Gen_Service_Observation_Assoc] [int] NULL,
[Gen_Service_Observation_1] [tinyint] NULL,
[Gen_Service_Observation_1_a] [text] NULL,
[Gen_Service_Observation_1_b] [text] NULL,
[Gen_Service_Observation_1_b_Gender] [text] NULL,
[Gen_Service_Observation_1_b_Hair] [text] NULL,
[Gen_Service_Observation_1_b_Hair_Color] [text] NULL,
[Gen_Service_Observation_1_b_Height] [text] NULL,
[Gen_Service_Observation_2] [tinyint] NULL,
[Gen_Service_Observation_2_a] [text] NULL,
[Gen_Service_Observation_3] [tinyint] NULL,
[Gen_Service_Observation_3_a] [text] NULL,
[Gen_Service_Observation_4] [tinyint] NULL,
[Gen_Service_Observation_4_a] [text] NULL,
[Gen_Service_Observation_5] [tinyint] NULL,
[Gen_Service_Observation_5_a] [text] NULL,
[Gen_Service_Observation_6] [tinyint] NULL,
[Gen_Service_Observation_6_a] [text] NULL,
[Sales_Floor_Associate_1] [tinyint] NULL,
[Sales_Floor_Associate_1_a] [tinyint] NULL,
[Sales_Floor_Associate_1_b] [tinyint] NULL,
[Sales_Floor_Associate_2] [tinyint] NULL,
[Sales_Floor_Associate_3] [tinyint] NULL,
[Sales_Floor_Associate_4] [tinyint] NULL,
[Sales_Floor_Associate_5] [text] NULL,
[Sales_Floor_Associate_6_a] [tinyint] NULL,
[Sales_Floor_Associate_6_a_a] [text] NULL,
[Sales_Floor_Associate_6_b] [tinyint] NULL,
[Sales_Floor_Associate_6_b_a] [text] NULL,
[Sales_Floor_Associate_6_c] [tinyint] NULL,
[Sales_Floor_Associate_6_c_a] [text] NULL,
[Sales_Floor_Associate_6_d] [tinyint] NULL,
[Sales_Floor_Associate_6_d_a] [text] NULL,
[Sales_Floor_Associate_7] [tinyint] NULL,
[Sales_Floor_Associate_7_a] [text] NULL,
[Sales_Floor_Associate_8] [text] NULL,
[Sales_Floor_Associate_9] [tinyint] NULL,
[Sales_Floor_Associate_9_a] [text] NULL,
[Sales_Floor_Associate_10] [tinyint] NULL,
[Sales_Floor_Associate_10_a] [text] NULL,
[Sales_Floor_Associate_10_a_1] [text] NULL,
[Sales_Floor_Associate_10_b_1] [text] NULL,
[Sales_Floor_Associate_10_c] [tinyint] NULL,
[Sales_Floor_Associate_10_c_a] [text] NULL,
[Sales_Floor_Associate_11] [tinyint] NULL,
[Sales_Floor_Associate_11_a] [text] NULL,
[Sales_Floor_Associate_12] [tinyint] NULL,
[Sales_Floor_Associate_12_a] [text] NULL,
[Sales_Floor_Associate_Comments] [ntext] NULL,
[Check_Out_1] [int] NULL,
[Check_Out_2] [int] NULL,
[Check_Out_3] [tinyint] NULL,
[Check_Out_4_a] [tinyint] NULL,
[Check_Out_4_b] [text] NULL,
[Check_Out_4_c] [text] NULL,
[Check_Out_4_d] [tinyint] NULL,
[Check_Out_5] [tinyint] NULL,
[Check_Out_5_a] [bit] NULL,
[Check_Out_5_b] [bit] NULL,
[Check_Out_5_c] [bit] NULL,
[Check_Out_5_d] [bit] NULL,
[Check_Out_5_Other] [text] NULL,
[Check_Out_6] [tinyint] NULL,
[Check_Out_7] [tinyint] NULL,
[Check_Out_8] [tinyint] NULL,
[Check_Out_9] [tinyint] NULL,
[Check_Out_10] [tinyint] NULL,
[Check_Out_11_a] [tinyint] NULL,
[Check_Out_11_a_a] [text] NULL,
[Check_Out_11_b] [tinyint] NULL,
[Check_Out_11_b_a] [text] NULL,
[Check_Out_11_c] [tinyint] NULL,
[Check_Out_11_c_a] [text] NULL,
[Check_Out_11_d] [tinyint] NULL,
[Check_Out_11_d_a] [text] NULL,
[Check_Out_12] [tinyint] NULL,
[Check_Out_12_a] [text] NULL,
[Check_Out_13] [tinyint] NULL,
[Check_Out_14] [tinyint] NULL,
[Check_Out_15] [tinyint] NULL,
[Check_Out_16_a] [tinyint] NULL,
[Check_Out_16_a_a] [text] NULL,
[Check_Out_16_b] [tinyint] NULL,
[Check_Out_16_b_a] [text] NULL,
[Check_Out_16_c] [tinyint] NULL,
[Check_Out_16_c_a] [text] NULL,
[Check_Out_16_d] [tinyint] NULL,
[Check_Out_16_d_a] [text] NULL,
[Check_Out_16_e] [tinyint] NULL,
[Check_Out_16_e_a] [text] NULL,
[Check_Out_17] [tinyint] NULL,
[Check_Out_17_a] [text] NULL,
[Check_Out_18] [tinyint] NULL,
[Check_Out_18_a] [text] NULL,
[Check_Out_Comments] [ntext] NULL,
[Overall_Comments_1] [text] NULL,
[Overall_Comments_2] [text] NULL,
[Overall_Comments_3] [text] NULL,
[Overall_Comments_4_a] [int] NULL,
[Overall_Comments_4] [text] NULL,
[Overall_Comments_5_a] [int] NULL,
[Overall_Comments_5_a_Comments] [text] NULL,
[Overall_Comments_5_b] [int] NULL,
[Overall_Comments_5_b_Comments] [text] NULL,
[Evaluation_Summary] [ntext] NULL,
[Sales_Floor_Int_POINTS] [int] NULL,
[Check_Out_Int_POINTS] [int] NULL,
[Phone_POINTS] [int] NULL,
[Cust_Service_POINTS] [int] NULL,
[Exterior_POINTS] [int] NULL,
[Interior_POINTS] [int] NULL,
[Merch_Pres_POINTS] [int] NULL,
[Store_POINTS] [int] NULL,
[Total_POINTS] [int] NULL,
[Sales_Floor_Description] [text] NULL,
[Check_Out_Description] [text] NULL,
[Manager_Description] [text] NULL,
[Exterior_Combined_Comments] [ntext] NULL,
[Interior_Combined_Comments] [ntext] NULL,
[Merchandise_Pres_Combined_Comments] [ntext] NULL,
[Gen_Service_Combined_Comments] [ntext] NULL,
[Sales_Floor_Int_Combined_Comments] [ntext] NULL,
[Check_Out_Combined_Comments] [ntext] NULL,
[Phone_Call_3_Comments] [text] NULL,
[Phone_Call_5_Comments] [text] NULL,
[Phone_Call_7_Comments] [text] NULL,
[Merch_Pres_3_Comments] [text] NULL,
[Sales_Floor_Associate_2_Comments] [text] NULL,
[Sales_Floor_Associate_3_Comments] [text] NULL,
[Sales_Floor_Associate_4_Comments] [text] NULL,
[Check_Out_3_Comments] [text] NULL,
[Check_Out_4_Comments] [text] NULL,
[Check_Out_6_Comments] [text] NULL,
[Check_Out_7_Comments] [text] NULL,
[Check_Out_8_Comments] [text] NULL,
[Check_Out_9_Comments] [text] NULL,
[Check_Out_10_Comments] [text] NULL,
[Check_Out_13_Comments] [text] NULL,
[Check_Out_14_Comments] [text] NULL,
[Check_Out_15_Comments] [text] NULL,
[Phone_Call_Combined_Comments] [text] NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
February 9, 2009 at 12:21 am
hmm ... well normalized schema :sick:
Looks like someone migrated from access or so...
Let's see what we can do ... I'll be back 😀
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
February 9, 2009 at 6:16 am
LOL Yea about 7-8 years ago they migrated this whole system from access to sql server and it's just kept growing like it is. :crying:
I guess I should have just left well enough alone but i've been getting tired of casting to varchars just to do string manipulations and with the claims that ms will kill off text at some point i figured might as well start the process now when we have time to work out the kinks 🙂
thanks for the help!!!
February 11, 2009 at 4:06 am
As ALZDBA allready told, 8060 is the maximum number of characters that fit in NVARCHAR. If you really want to convert the NTEXT you must limit the amount of data. select left(fieldname, 80000) But you will lose all text beyond 8000 characters. If that is not an option, you have to stick with NTEXT.
February 11, 2009 at 5:04 am
HanShi (2/11/2009)
As ALZDBA allready told, 8060 is the maximum number of characters that fit in NVARCHAR. If you really want to convert the NTEXT you must limit the amount of data.select left(fieldname, 80000)But you will lose all text beyond 8000 characters. If that is not an option, you have to stick with NTEXT.
No, I said 8060 if the maximum row length for fixed length columns.
(N)Text needs to be replaced with (N)varchar(max). Those can contain up to 2GB of data (1 for Nvar..)
Even better. With SQL2005 if a varchar(5000) column doesn't fit into the row's first 8060 bytes, it will get relocated off-row.
Check out
sp_tableoption N'MyTable', 'large value types out of row', 'ON'
in Books online !
Edited:
Another question: Has de DBLevel been set to 90 (SQL2005) ??
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
February 11, 2009 at 5:49 am
ALZDBA (2/11/2009)
No, I said 8060 if the maximum row length for fixed length columns.(N)Text needs to be replaced with (N)varchar(max). Those can contain up to 2GB of data (1 for Nvar..)
Sorry, you are right. I remembered wrongly what I have learned in the past.:blush:
February 11, 2009 at 6:44 am
ALZDBA (2/11/2009)
Edited:
Another question: Has de DBLevel been set to 90 (SQL2005) ??
yes the dblevel is set to sql2005 (90)
I've done some testing and it seems to be an issue that this happens only when you try to alter the table to convert the (n)text to (n)vachar(max). whatever sql server does internally it is taking an unknown amount of space in the row.
where as text is always listed as using 16bytes of data in the sys.columns varchar(max) is listed as -1 and i am unable to determine what a converted column is actually using as space. it appears to variablly adjust based on current usage or something...
I was able to do a create table statement where i replaced all text with varchar(max) and the table created fine. then I copied the data from the old table to the new table and again everything worked fine.
so looks like it's just a matter of recreating tables and copying data over instead of just altering the columns.
February 11, 2009 at 8:00 am
HanShi (2/11/2009)
Sorry, you are right. I remembered wrongly what I have learned in the past.:blush:
No worries ..... been there, done that :hehe:
abarber,
I'm glad you managed the work around.
Some times, that's the best way to go.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply