sql server - Maximum row size of 8060 Error -
i stuck , can not figure out. have ideas? appreciate help.
i have table in sql server 2008 following defintion:
i can insert/delete/update without error. however, when run select statement filter out 1 single item, got following error:
msg 511, level 16, state 1, line 1 cannot create row of size 8104 greater allowable maximum row size of 8060.
if remove condition a15.member_parent_level_id4 in (91329) ran fine. if add 5 or 10 criterias ran fine, including 1 throwing error if add itself.
[mstr_prod_hier_id] [int] not null, [mstr_prod_hier_desc] [varchar](50) null, [member_group_level_id1] [int] null, [member_group_level_desc1] [varchar](60) null, [memberkey_sort1] [int] null, [member_group_level_id2] [int] null, [member_group_level_desc2] [varchar](60) null, [memberkey_sort2] [int] null, [member_group_level_id3] [int] null, [member_group_level_desc3] [varchar](60) null, [memberkey_sort3] [int] null, [member_level_id4] [int] null, [member_level_desc4] [varchar](60) null, [member_level_desc4_1] [varchar](60) null, [member_parent_level_id1] [int] not null, [member_parent_level_desc1] [varchar](60) null, [sort1] [int] null, [member_parent_level_id2] [int] null, [member_parent_level_desc2] [varchar](60) null, [sort2] [int] null, [member_parent_level_id3] [int] null, [member_parent_level_desc3] [varchar](60) null, [sort3] [int] null, [member_parent_level_id4] [int] null, [member_parent_level_desc4] [varchar](60) null, [sort4] [int] null, [class_desc] [varchar](60) null, [class_desc1] [varchar](50) null, [total_brand_ind] [int] not null } { select a11.planformat planformat, max(a18.formatname) formatname, a15.member_parent_level_id2 member_parent_level_id2, max(a15.member_parent_level_desc2) member_parent_level_desc2, a15.member_parent_level_id3 member_parent_level_id3, max(a15.member_parent_level_desc3) member_parent_level_desc3, a15.member_parent_level_id4 member_parent_level_id4, max(a15.member_parent_level_desc4) member_parent_level_desc4, max(a15.class_desc) class_desc, max(a15.class_desc1) class_desc1, a16.mstr_season_id mstr_season_id, max(a16.mstr_season_desc) mstr_season_desc, max(a16.mstr_season_desc1) mstr_season_desc1, a17.mstr_store_id mstr_store_id, max(a17.mstr_store_desc1) mstr_store_desc1, a13.mstr_c_mnth_id mstr_c_mnth_id, max(a13.mstr_c_mnth_desc) mstr_c_mnth_desc, a13.mstr_calendar_grp_id mstr_calendar_grp_id, max(a13.mstr_calendar_grp_desc) mstr_calendar_grp_desc, a12.year_nbr year_nbr, a11.planno planno, max(a18.planname) planname, sum((a11.value1821 / 1000.0)) act_proj_sls_dol a_mstr_plan_fct a11 join a_mstr_calendar_month_xref a12 on (a11.member2 = a12.member2) join a_mstr_calendar_month a13 on (a12.dim_time_id = a13.dim_time_id , a12.year_nbr = a13.year_nbr) join a_mstr_product_level_3 a14 on (a11.member0 = a14.p_member3) join a_mstr_product_level_3_parent a15 on (a14.member_level_id4 = a15.member_level_id4 , a14.mstr_prod_hier_id = a15.mstr_prod_hier_id) join a_mstr_seasonality a16 on (a11.member3 = a16.member3) join a_mstr_store a17 on (a11.member1 = a17.member1) join a_mstr_plan a18 on (a11.planno = a18.planno , a12.year_nbr = a18.year_nbr , a13.year_id = a18.year_id) (a11.planno in (1547) , a15.member_parent_level_id4 in (91329)) group a11.planformat, a15.member_parent_level_id2, a15.member_parent_level_id3, a15.member_parent_level_id4, a16.mstr_season_id, a17.mstr_store_id, a13.mstr_c_mnth_id, a13.mstr_calendar_grp_id, a12.year_nbr, a11.planno } this follow up. previous post. mentioned below, error goes away after dropped table, perform dbcc clean , recreate table. now, coming time, when select 2 or more criterias. problem seems condition filter out product. product table not have nvarchar or varbinary. frustrated... :-(
i suggest modifying query remove in clause(s) , replace additional joins or exists. may generate more efficient query plan avoids problem you're seeing.
Comments
Post a Comment