Solved

Cannot sort a row of size 8095

Posted on 2009-05-06
12
551 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
  • 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
 
LVL 3

Author Comment

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

Accepted Solution

by:
RiteshShah earned 500 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
What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

 
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

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed

706 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

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now