Solved

Cannot sort a row of size 8095

Posted on 2009-05-06
12
553 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
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
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

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

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

Suggested Solutions

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

914 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

14 Experts available now in Live!

Get 1:1 Help Now