Cannot sort a row of size 8397, which is greater than the allowable maximum of 8094.  
Author Message
UmmagummA





PostPosted: Wed Feb 11 09:11:53 CST 2004 Top

SQL Server Developer >> Cannot sort a row of size 8397, which is greater than the allowable maximum of 8094.

Hi all
It seesm that we have reached a sql limitation with a complex query.

Any one have any ideas how to resolve this is sql 2000?

Greatly apprecoated.

Sample query
Select SectionID, Section, Description, QuestionID, Question, DisplayType,
Designation, Orientation, ResponseID, 0 As NoOfVotes, Response,
ResponseScore, IsCorrect, DisplayEvaluator, NULL As EmployeeName, NULL As
EvaluatorName, Indent, HideLine From EvalContent Where CompanyID =
'2003021254' And EvalID = 57 And DisplayType Not In ('IB', 'TA') UNION
Select SectionID, Section, '' As Description, QuestionID, Question,
DisplayType, Designation, Orientation, ResponseID, Sum(NoOfVotes) As
NoOfVotes, Response, ResponseScore, IsCorrect, DisplayEvaluator,
EmployeeName, EvaluatorName, Indent, HideLine From
v_EvalResults_EmployeeEvaluatorDistinct Where CompanyID = '2003021254' And
EvalID = 57 And EmployeeID = '1281' And EvaluatorID = '1281' And IsStarted =
1 And IsFinished = 1 And IsDeclined = 0 And IsExcluded <> 1 GROUP By
SectionID, Section, QuestionID, Question, DisplayType, Designation,
Orientation, ResponseID, Response, ResponseScore, IsCorrect,
DisplayEvaluator, EmployeeName, EvaluatorName, Indent, HideLine Order By
SectionID, QuestionID, ResponseID, NoOfVotes , EvaluatorName

SQL Server203  
 
 
Jacco





PostPosted: Wed Feb 11 09:11:53 CST 2004 Top

SQL Server Developer >> Cannot sort a row of size 8397, which is greater than the allowable maximum of 8094. Technically it is a limitation in SQL Server, practically I would call it a
flaw in the database design. Your query only returns 18 columns, at least 6
of which are numeric or short character columns. Are you sure you need about
8350 bytes for those other 12 columns, or about 700 bytes each?

--
Jacco Schalkwijk
SQL Server MVP




> Hi all
> It seesm that we have reached a sql limitation with a complex query.
>
> Any one have any ideas how to resolve this is sql 2000?
>
> Greatly apprecoated.
>
> Sample query
> Select SectionID, Section, Description, QuestionID, Question, DisplayType,
> Designation, Orientation, ResponseID, 0 As NoOfVotes, Response,
> ResponseScore, IsCorrect, DisplayEvaluator, NULL As EmployeeName, NULL As
> EvaluatorName, Indent, HideLine From EvalContent Where CompanyID =
> '2003021254' And EvalID = 57 And DisplayType Not In ('IB', 'TA') UNION
> Select SectionID, Section, '' As Description, QuestionID, Question,
> DisplayType, Designation, Orientation, ResponseID, Sum(NoOfVotes) As
> NoOfVotes, Response, ResponseScore, IsCorrect, DisplayEvaluator,
> EmployeeName, EvaluatorName, Indent, HideLine From
> v_EvalResults_EmployeeEvaluatorDistinct Where CompanyID = '2003021254' And
> EvalID = 57 And EmployeeID = '1281' And EvaluatorID = '1281' And IsStarted
=
> 1 And IsFinished = 1 And IsDeclined = 0 And IsExcluded <> 1 GROUP By
> SectionID, Section, QuestionID, Question, DisplayType, Designation,
> Orientation, ResponseID, Response, ResponseScore, IsCorrect,
> DisplayEvaluator, EmployeeName, EvaluatorName, Indent, HideLine Order By
> SectionID, QuestionID, ResponseID, NoOfVotes , EvaluatorName
>
>


 
 
Tibor





PostPosted: Wed Feb 11 09:21:58 CST 2004 Top

SQL Server Developer >> Cannot sort a row of size 8397, which is greater than the allowable maximum of 8094. In addition to Jacco's post:

In case SQL Server creates some intermediate worktable, you can try the
ROBUST PLAN optimizer hint.

--
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver




> Hi all
> It seesm that we have reached a sql limitation with a complex query.
>
> Any one have any ideas how to resolve this is sql 2000?
>
> Greatly apprecoated.
>
> Sample query
> Select SectionID, Section, Description, QuestionID, Question, DisplayType,
> Designation, Orientation, ResponseID, 0 As NoOfVotes, Response,
> ResponseScore, IsCorrect, DisplayEvaluator, NULL As EmployeeName, NULL As
> EvaluatorName, Indent, HideLine From EvalContent Where CompanyID =
> '2003021254' And EvalID = 57 And DisplayType Not In ('IB', 'TA') UNION
> Select SectionID, Section, '' As Description, QuestionID, Question,
> DisplayType, Designation, Orientation, ResponseID, Sum(NoOfVotes) As
> NoOfVotes, Response, ResponseScore, IsCorrect, DisplayEvaluator,
> EmployeeName, EvaluatorName, Indent, HideLine From
> v_EvalResults_EmployeeEvaluatorDistinct Where CompanyID = '2003021254' And
> EvalID = 57 And EmployeeID = '1281' And EvaluatorID = '1281' And IsStarted
=
> 1 And IsFinished = 1 And IsDeclined = 0 And IsExcluded <> 1 GROUP By
> SectionID, Section, QuestionID, Question, DisplayType, Designation,
> Orientation, ResponseID, Response, ResponseScore, IsCorrect,
> DisplayEvaluator, EmployeeName, EvaluatorName, Indent, HideLine Order By
> SectionID, QuestionID, ResponseID, NoOfVotes , EvaluatorName
>
>


 
 
Delbert





PostPosted: Wed Feb 11 10:20:22 CST 2004 Top

SQL Server Developer >> Cannot sort a row of size 8397, which is greater than the allowable maximum of 8094. Did it really say 8094?

---

Do you really what UNION instead of UNION ALL?

---

Remove columns such as
Section, Question, Response, ResponseScore, (may be IsCorrect too, etc)
from the GROUP BY list to avoid unnessarily hitting the limit.

But you'll still need to figure out what you really want.
Do you even need a GROUP BY?
Do you want to total the number votes?
Should something other the simple addition
be done with ResponseScore and IsCorrect?

Bye,
Delbert Glass



> Hi all
> It seesm that we have reached a sql limitation with a complex query.
>
> Any one have any ideas how to resolve this is sql 2000?
>
> Greatly apprecoated.
>
> Sample query
> Select SectionID, Section, Description, QuestionID, Question, DisplayType,
> Designation, Orientation, ResponseID, 0 As NoOfVotes, Response,
> ResponseScore, IsCorrect, DisplayEvaluator, NULL As EmployeeName, NULL As
> EvaluatorName, Indent, HideLine From EvalContent Where CompanyID =
> '2003021254' And EvalID = 57 And DisplayType Not In ('IB', 'TA') UNION
> Select SectionID, Section, '' As Description, QuestionID, Question,
> DisplayType, Designation, Orientation, ResponseID, Sum(NoOfVotes) As
> NoOfVotes, Response, ResponseScore, IsCorrect, DisplayEvaluator,
> EmployeeName, EvaluatorName, Indent, HideLine From
> v_EvalResults_EmployeeEvaluatorDistinct Where CompanyID = '2003021254' And
> EvalID = 57 And EmployeeID = '1281' And EvaluatorID = '1281' And IsStarted
=
> 1 And IsFinished = 1 And IsDeclined = 0 And IsExcluded <> 1 GROUP By
> SectionID, Section, QuestionID, Question, DisplayType, Designation,
> Orientation, ResponseID, Response, ResponseScore, IsCorrect,
> DisplayEvaluator, EmployeeName, EvaluatorName, Indent, HideLine Order By
> SectionID, QuestionID, ResponseID, NoOfVotes , EvaluatorName
>
>