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

Popular posts from this blog

Issues changing the value of an element in an array in matlab -

-
i'm trying create program sum n-1 , n-2 numbers create n , store value in array. currently, output (assuming numdisp > 2 ) num1 , num2 , 0 , 0 , 0 ... size of array correct, i'm having issues reassigning elements. num1 = input('input first integer of fibonacci sequence: '); num2 = input('input second integer of fibonacci sequence: '); numsdisp = input('how many integers display? --> '); list = zeros(numsdisp,1); list(1) = num1; list(2) = num2; if (numsdisp <=0) disp('value must integer greater zero.') elseif (numsdisp == 1) disp(num1) elseif (numsdisp == 2) disp(num1) disp(num2) else disp(num1) disp(num2) index = 3:numsdisp disp(list(index)) list(index) = list((index-1)) + list((index-2)); end end example input: 3 3 4 corresponding output: 3 3 0 0 you aren't printing out final result, intermediate steps. in for loop, printing list(index) updating value, rather updat...
Read more

php - MySQLi binding parameters in a prepared statement doesn't work unless inserted after "WHERE" -

-
i have php code pulls info database. uses prepared statement, works fine written below: <?php $n = '5'; if ($stmt = $connection->prepare("select title items id = ?")) { $stmt->bind_param("s", $n); $stmt->execute(); $stmt->bind_result($title); while ($stmt->fetch()) { echo $title; } $stmt->close(); } ?> and let's echo "batman". however, when move placeholder here: "select ? items id = 5" and change: $n = 'title'; instead of echoing "batman", echoes "title". not possible use placeholder select parameter? what have done this: select "title" items id = 5 you cant use prepared statement bindings identifiers, parameters.
Read more

vb.net - Alternative to the T-SQL AS keyword -

-
please see code below: imports system.data.sqlclient imports system.configuration public class form1 private _constring string private sub form1_load(byval sender object, byval e system.eventargs) handles me.load dim objdr sqldatareader dim objcommand sqlcommand dim objcon sqlconnection dim id integer try _constring = configurationmanager.connectionstrings("testconnection").tostring objcon = new sqlconnection(_constring) objcommand = new sqlcommand("select person.urn, car.urn person inner join car on person.urn = car.urn , personid=1") objcommand.connection = objcon objcon.open() objdr = objcommand.executereader(connectionstate.closed) while objdr.read id = objdr("urn") 'line 19 loop objdr.close() ...
Read more