Cannot sort a row which is greater than 8094?

I've recently started getting the following error when I retrieve
long text entries from my MS SQL Server database...
"Cannot sort a row of size 8150, which is greater than the allowable
maximum of 8094"
I have one column in one of the tables involved in the SELECT statement
that has a maximum of 8000 characters.
Should I resize this column to 7800 or similar, to avoid this problem,
or should I modify my SELECT statement to ignore the errors and proceed
as normal?
Thanks.
--
fiddlewidawiddumInformation from the SQL Server Books Onine (SELECT, ORDER BY clause):
There is no limit to the number of items in the ORDER BY clause. However,
there is a limit of 8,060 bytes for the row size of intermediate worktables
needed for sort operations. This limits the total size of columns specified
in an ORDER BY clause.
You should set the column width accordingly.
Martin C K Poon
Senior Analyst Programmer
====================================
"Stimp" <ren@.spumco.com> bl
news:slrne3u0f8.vf.ren@.carbon.redbrick.dcu.ie g...
> I've recently started getting the following error when I retrieve
> long text entries from my MS SQL Server database...
> "Cannot sort a row of size 8150, which is greater than the allowable
> maximum of 8094"
> I have one column in one of the tables involved in the SELECT statement
> that has a maximum of 8000 characters.
> Should I resize this column to 7800 or similar, to avoid this problem,
> or should I modify my SELECT statement to ignore the errors and proceed
> as normal?
> Thanks.
> --
> fiddlewidawiddum|||Do you have to order by the full length of 8000 character column? Maybe
left(<colum>, 2000)
could be just fine.
ML
http://milambda.blogspot.com/|||On Fri, 14 Apr 2006 ML <ML@.discussions.microsoft.com> wrote:
> Do you have to order by the full length of 8000 character column? Maybe
> left(<colum>, 2000)
I order by the unique identifier column... not the varchar column.
I get the impression that the SELECT statement is pulling back several
rows (including the 8000 character one) before it sorts through them and
so gives me this error if the 8000 character column is filled to its
maximum capacity AND another varchar column (which is included in
the SELECT statement) has more than 94 chars.
fiddlewidawiddum|||Well, post the query then, so we can have a look.
Sorting is done implicitly by the Query Optimizer when GROUP BY or DISTINCT
is used in the query.
ML
http://milambda.blogspot.com/|||On Fri, 14 Apr 2006 ML <ML@.discussions.microsoft.com> wrote:
> Well, post the query then, so we can have a look.
> Sorting is done implicitly by the Query Optimizer when GROUP BY or DISTINC
T
> is used in the query.
SELECT TOP 6 d.EntryTitle, d.EntryText, d.EntryDate, l.LocName, c.CountryNam
e
FROM Diary d, Location l, Country c
WHERE d.idLocation = l.idLocation AND
d.idCountry = c.idCountry
ORDER BY d.idDiary desc
EntryTitle is 255 chars long (although I'll probably resize this to 65)
EntryText was 8000 chars long, but I've since resized it to 7900
fiddlewidawiddum|||Judging by the simplicity of the query, I guess the problem originates in th
e
execution plan. Try this:
SELECT TOP 6 d.EntryTitle, d.EntryText, d.EntryDate, l.LocName, c.CountryNam
e
FROM Diary d
inner join Location l on d.idLocation = l.idLocation
inner join Country c on d.idCountry = c.idCountry
ORDER BY d.idDiary desc
Here I've used the contemporary join syntax that may help the Query
Optimizer to build a different execution plan.
ML
http://milambda.blogspot.com/|||On Fri, 14 Apr 2006 ML <ML@.discussions.microsoft.com> wrote:
> Judging by the simplicity of the query, I guess the problem originates in
the
> execution plan. Try this:
> SELECT TOP 6 d.EntryTitle, d.EntryText, d.EntryDate, l.LocName, c.CountryN
ame
> FROM Diary d
> inner join Location l on d.idLocation = l.idLocation
> inner join Country c on d.idCountry = c.idCountry
> ORDER BY d.idDiary desc
> Here I've used the contemporary join syntax that may help the Query
> Optimizer to build a different execution plan.
hmm, I assumed that my query would work in a similar way to having
JOINS.
I'll try that if the error crops up again (it's since disappeared once
I resized the column to 7900 and trimmed a few rows that had entries >
7900).
cheers!
--
fiddlewidawiddum|||You could try adding the ROBUST PLAN hint and see if it helps.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Stimp" <ren@.spumco.com> wrote in message news:slrne3u0f8.vf.ren@.carbon.redbrick.dcu.ie...[
color=darkred]
> I've recently started getting the following error when I retrieve
> long text entries from my MS SQL Server database...
> "Cannot sort a row of size 8150, which is greater than the allowable
> maximum of 8094"
> I have one column in one of the tables involved in the SELECT statement
> that has a maximum of 8000 characters.
> Should I resize this column to 7800 or similar, to avoid this problem,
> or should I modify my SELECT statement to ignore the errors and proceed
> as normal?
> Thanks.
> --
> fiddlewidawiddum[/color]