Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Cannot sort a row of size 8095

Posted on 2009-05-06
12
Medium Priority
?
562 Views
Last Modified: 2012-06-27
Hi all,

I have the attached select query which is giving me the following error:

Cannot sort a row of size 8095, which is greater than the allowable maximum 8094

This error is directly related to the column VALUE which is made up of ORDER_QTY*UNIT_PRICE. This should simply return the value of a purchase order line. If I take this out, the query runs fine.

Also, If I run this query with the UNIT_PRICE and ORDER_QTY columns serperately, saving as a view and then run a select from the view which performs the multiple between the two columns all is fine.

Any ideas guys?
SELECT DISTINCT 
                      TOP 100 PERCENT dbo.PURC_ORDER_LINE.USER_1, dbo.PURCHASE_ORDER.VENDOR_ID, dbo.PURCHASE_ORDER.ID, 
                      dbo.PURC_ORDER_LINE.LINE_NO, LTRIM(ISNULL(dbo.PURC_ORDER_LINE.GL_EXPENSE_ACCT_ID, '') + ' ' + ISNULL(dbo.PART.MAT_GL_ACCT_ID, '')) 
                      AS CODE, dbo.PURC_ORDER_LINE.UNIT_PRICE * dbo.PURC_ORDER_LINE.ORDER_QTY AS VALUE, dbo.PURC_ORDER_LINE.ORDER_QTY, 
                      dbo.PURC_ORDER_LINE.PURCHASE_UM, dbo.PURCHASE_ORDER.DESIRED_RECV_DATE, dbo.PURC_ORDER_LINE.VAT_CODE, 
                      LTRIM(ISNULL(dbo.PURC_ORDER_LINE.PART_ID, '') + ' ' + ISNULL(dbo.PURC_LINE_BINARY_VIEW.PART_STRING, '') 
                      + ' ' + ISNULL(dbo.PART.DESCRIPTION, '') + ' ' + ISNULL(dbo.PURC_ORDER_LINE.USER_3, '')) AS DESCRIPTION
FROM         dbo.PURCHASE_ORDER INNER JOIN
                      dbo.PURC_ORDER_LINE ON dbo.PURCHASE_ORDER.ID = dbo.PURC_ORDER_LINE.PURC_ORDER_ID INNER JOIN
                      dbo.VW_SUMMIT_AUDIT_PO ON dbo.PURCHASE_ORDER.ID = dbo.VW_SUMMIT_AUDIT_PO.ORDER_NO AND 
                      dbo.PURC_ORDER_LINE.LINE_NO = dbo.VW_SUMMIT_AUDIT_PO.LINE_NO LEFT OUTER JOIN
                      dbo.PURC_LINE_BINARY_VIEW ON dbo.PURC_ORDER_LINE.PURC_ORDER_ID = dbo.PURC_LINE_BINARY_VIEW.PURC_ORDER_ID AND 
                      dbo.PURC_ORDER_LINE.LINE_NO = dbo.PURC_LINE_BINARY_VIEW.PURC_ORDER_LINE_NO LEFT OUTER JOIN
                      dbo.PART ON dbo.PURC_ORDER_LINE.PART_ID = dbo.PART.ID
ORDER BY dbo.PURCHASE_ORDER.ID, dbo.PURC_ORDER_LINE.LINE_NO

Open in new window

0
Comment
Question by:FMabey
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 6
12 Comments
 
LVL 31

Expert Comment

by:RiteshShah
ID: 24312488
because you are crossing limit of allowed byte per row, you may convert some of the big varchar field to TEXT and get rid of this issue.
0
 
LVL 31

Expert Comment

by:RiteshShah
ID: 24312496
actually SQL Server stored your data into 8KB pages (8060 bytes) and I am sure you may have few columns with big varchar so I would like you to change it to Text. BTW, what version of SQL Serevr you are using?
0
 
LVL 3

Author Comment

by:FMabey
ID: 24312508
But the error only started happening when I carried out the sum ORDER_QTY*UNIT_PRICE... This is multiplying two decmial fields together.
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 3

Author Comment

by:FMabey
ID: 24312512
SQL Server 2000
0
 
LVL 31

Accepted Solution

by:
RiteshShah earned 1500 total points
ID: 24312514
not only that, if put ORDER_QTY*UNIT_PRICE and remove any one or two big varchar field, you will get results for sure.
0
 
LVL 3

Author Comment

by:FMabey
ID: 24312541
Yep, that worked...

So how d I convert the part:

LTRIM(ISNULL(dbo.PURC_ORDER_LINE.PART_ID, '') + ' ' + ISNULL(dbo.PURC_LINE_BINARY_VIEW.PART_STRING, '')
                      + ' ' + ISNULL(dbo.PART.DESCRIPTION, '') + ' ' + ISNULL(dbo.PURC_ORDER_LINE.USER_3, '')) AS DESCRIPTION

into TEXT?
0
 
LVL 31

Expert Comment

by:RiteshShah
ID: 24312575
you can change data type of the column from big varchar to TEXT, not all varchar which are small but only big varchar. may varchar(1000) or varchar(3000) etc.
0
 
LVL 3

Author Comment

by:FMabey
ID: 24312598
But how, in the select statement, do I do that?
0
 
LVL 31

Expert Comment

by:RiteshShah
ID: 24312606
well, you can do in select statement but it will make heavy load on the server, you can change it like


ALTER TABLE PURC_ORDER_LINE
ALTER COLUMN LINE_1 TEXT

by assuming LINE_1 fileld is big varchar field,
0
 
LVL 3

Author Comment

by:FMabey
ID: 24312628
I can't alter the table PURC_ORDER_LINE, it is part of a 3rd party system.

I'm still not clear as to why I cannot perform both the text concatenation and the multiplication in the same select query... Surely there must be a way of doing this which doesn't involve changing the tables used in the select?
0
 
LVL 31

Expert Comment

by:RiteshShah
ID: 24312635
in that case you can take one chance by doing like.


select cast(Line_1 as text) from  PURC_ORDER_LINE

cast your every big varchar field.
0
 
LVL 3

Author Comment

by:FMabey
ID: 24323565
Just to comment...

In the end I used two views. I did the bulk of my work in the 1st view and then just carried out the multiplication i the 2nd. This works fine.
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

618 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question