[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 565
  • Last Modified:

Cannot sort a row of size 8095

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
FMabey
Asked:
FMabey
  • 6
  • 6
1 Solution
 
RiteshShahCommented:
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
 
RiteshShahCommented:
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
 
FMabeyAuthor Commented:
But the error only started happening when I carried out the sum ORDER_QTY*UNIT_PRICE... This is multiplying two decmial fields together.
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
FMabeyAuthor Commented:
SQL Server 2000
0
 
RiteshShahCommented:
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
 
FMabeyAuthor Commented:
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
 
RiteshShahCommented:
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
 
FMabeyAuthor Commented:
But how, in the select statement, do I do that?
0
 
RiteshShahCommented:
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
 
FMabeyAuthor Commented:
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
 
RiteshShahCommented:
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
 
FMabeyAuthor Commented:
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

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

  • 6
  • 6
Tackle projects and never again get stuck behind a technical roadblock.
Join Now