Cannot sort a row of size ### Error

[JayKusch]

Afternoon Folk ...

have a query that is bombing out w/ an error message that read:

Server: Msg 1540, Level 16, State 1, Line 1
Cannot sort a row of size 8550, which is greater than the allowable maximum of 8094.


This is the Query:

SELECT DISTINCT p.Alias, p.Name, p.Description, m.Keywords,
s.store_image
FROM Partner p
NNER JOIN MerchantKeywordsAgr m ON p.PartnerId =
m.PartnerIdMerch
INNER JOIN StoresNew.dbo.stores_master_lo s ON p.Alias =
REPLACE(s.store_link, 'usb/ProxyFrameset.jsp?Vendor=', '')


Here is the table structure:

CREATE TABLE [dbo].[MerchantKeywordsAGR] (
[PartnerIdMerch] [numeric](12, 0) NOT NULL ,
[Keywords] [varchar] (8000) COLLATE
SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Nbr_Of_Keywords] [numeric](18, 0) NULL
) ON [PRIMARY]


we do have records with over 7900 bytes in the Keywords field, so i cannot reduce the size of the field.


Any pointers would be great!

Thanks

 

[tlbroadbent]

Using the DISTINCT clause causes the SORT. And SQL will not SORT more than 8094 characters. If DISTINCT is not needed remove it. If you need to Eliminate duplicates, can you use a string function to select part of Keywords column. The following example uses the Left function. You made need to adjust the number of characters selected.

SELECT DISTINCT
p.Alias,
p.Name,
p.Description,
Left(m.Keywords,6500) As Keywords,
s.store_image
FROM Partner p
NNER JOIN MerchantKeywordsAgr m
ON p.PartnerId = m.PartnerIdMerch
INNER JOIN StoresNew.dbo.stores_master_lo s
ON p.Alias =
REPLACE(s.store_link, 'usb/ProxyFrameset.jsp?Vendor=', '') Terry L. Broadbent
Programming and Computing Resources

http://tlbroadbent.home.attbi.com/prog.htm

 

[JayKusch]

Well Terry ... even w/out the DISTINCT the error message prevails. tried w/ the Left(*) on keywords w/ no luck either. We do need the full 8000 on keywords to be pulled. i tried to substring it with, and as o figured, it failed too. Thanks for the help thus far!

 

[tlbroadbent]

Which version of SQL are you running? Do you have any Group By or Order By clauses on the query? How do you execute the query? Terry L. Broadbent
Programming and Computing Resources

http://tlbroadbent.home.attbi.com/prog.htm

 

[JayKusch]

No Order By and No Group By ... we found that the other fields we are extracting account for 700 chars. we then subtracted 8000 by 700 and then looked for all keywords that were > 7300. Found 3. we put a <> in the query and BINGO! this remedies our issue for now but ... in the future we may be bite again.

we are on SQL2K SP2, running on W2K Adv Server. Running this, for now, in Query Analyzer.

I will keep on investigating! thanks a bunch bud!