Solved

Cannot sort a row of size 8095

Posted on 2009-05-06
12
557 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
PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

 
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
 
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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQL Server Agent Job Error 13 79
SQL / Table Lock? 7 40
Question about Common Table Expressions 3 43
Backing up Large SQL Server VM Best practice [using Veeam Backup] 8 71
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

734 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