Error Sorting

I need the results of a view sorted by date. When the order by date is added
to the view I get the following error from SQL/ODBC SQL Driver:
"cannot sort a row of size 8813, which is greater than the allowable maximum
of 8094"
Is there a way around this?
Thanks
Niles wrote:
> I need the results of a view sorted by date. When the order by date
> is added to the view I get the following error from SQL/ODBC SQL
> Driver: "cannot sort a row of size 8813, which is greater than the
> allowable maximum of 8094"
> Is there a way around this?
> Thanks
You can cut down the result set row size. Why is the row so large?
David Gugick
Imceda Software
www.imceda.com
|||I reduced the number of columns returned and it works fine now. Why is the
number of columns a factor here?
Thanks
"David Gugick" wrote:

> Niles wrote:
> You can cut down the result set row size. Why is the row so large?
>
> --
> David Gugick
> Imceda Software
> www.imceda.com
>
|||Niles wrote:
> I reduced the number of columns returned and it works fine now. Why
> is the number of columns a factor here?
>
8060 is the maximum row size for SQL Server. It's not so much the number
of columns, but the total byte size of all columns in a row. While SQL
Server will allow you to create a table with varchar type columns that
could potentially add up to more than 8060 bytes, you do get a warning
when the table is created. If at any point you change data in a row or
attempt to insert more than 8060 bytes in a row, you get an error.
Since you didn't post any SQL, I can only assume that your table is as
described above, or your columns in the query from multiple tables are
large enough that you're reaching the SQL Server limit.
If you are dealing with a large table or a large table is responsible
for most of the row size, I would encourage you to consider redesigning
the table. Large row sizes mean very low page density. This leads to
excessive page reading on some queries and a general slowdown on the
server when the table is accessed. If you can, you could put some of
that large variable data in a text/ntext column or possibly in another
table.
If you want other suggestions, post your DDL and SQL.
David Gugick
Imceda Software
www.imceda.com
|||In addition to the other posts:
You could try the ROBUST PLAN optimizer hint.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Niles" <Niles@.discussions.microsoft.com> wrote in message
news:D1591C68-5A0E-41A0-A453-C8D7957E5F47@.microsoft.com...
>I need the results of a view sorted by date. When the order by date is added
> to the view I get the following error from SQL/ODBC SQL Driver:
> "cannot sort a row of size 8813, which is greater than the allowable maximum
> of 8094"
> Is there a way around this?
> Thanks