...greater than the allowable maximum of 8094 1

[Aeros]

I have a query that is giving me an error when I use the 'ORDER BY' function but is fine if I DONT use the 'ORDER BY' and I really have no idea why this would happen.

Useing this query without the 'ORDER BY' gives me a result of 32 rows:

[tt]
declare @sqlSearchParam varchar(500)
set @sqlSearchParam = 'audio'

SELECT dbo.Listings.*, dbo.[411SalesTable].Keywords, dbo.EDP_search.varKeywords, dbo.EDP_search.txtDescription, dbo.[411SalesTable].EndDate,
dbo.[411SalesTable].Impact, dbo.EDPage.intEnhanceTypeID, dbo.CategoryNames.varURL AS CategoryURL
FROM dbo.[411SalesTable] RIGHT OUTER JOIN
dbo.Listings ON dbo.[411SalesTable].ListingID = dbo.Listings.ListingID LEFT OUTER JOIN
dbo.EDPage INNER JOIN
dbo.GroupListings ON dbo.EDPage.intEDPageID = dbo.GroupListings.intEDPageID INNER JOIN
dbo.EDP_search ON dbo.EDPage.intEDPageID = dbo.EDP_search.intEDPageID ON dbo.Listings.ListingID = dbo.GroupListings.intListingID LEFT OUTER JOIN dbo.CategoryNames ON dbo.CategoryNames.intCID = dbo.Listings.CategoryID
WHERE (dbo.Listings.ListingName LIKE '%' + @sqlSearchParam + '%') OR
(dbo.Listings.CompanyName LIKE '%' + @sqlSearchParam + '%') OR
(dbo.Listings.SortName LIKE '%' + @sqlSearchParam + '%') OR
(dbo.Listings.Equipment LIKE '%' + @sqlSearchParam + '%') OR
(dbo.Listings.Description LIKE '%' + @sqlSearchParam + '%') OR
(dbo.Listings.Personnel LIKE '%' + @sqlSearchParam + '%') OR
(dbo.[411SalesTable].Keywords LIKE N'%' + @sqlSearchParam + N'%') OR
(dbo.EDP_search.varKeywords LIKE '%' + @sqlSearchParam + '%') OR
(dbo.EDP_search.txtDescription LIKE '%' + @sqlSearchParam + '%')
[/tt]

Adding this to the end:
ORDER BY dbo.[411SalesTable].Impact DESC, dbo.Listings.SortName
gives me the error:
[tt]
Server: Msg 1540, Level 16, State 1, Line 4
Cannot sort a row of size 8391, which is greater than the allowable maximum of 8094.[/tt]

This query has always worked in the past and has just started giving me problems recently. Any help greatly appreciated.

 

[SQLDenis]

from BOL

The ORDER BY clause sorts query results by one or more columns up to 8,060 bytes. For more information about the maximum ORDER BY clause size, see SELECT.

Try doing this instead (should work in theory)

ORDER BY left(Impact,100) DESC, left(SortName,100)

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[Aeros]

that seems to have helped. So I take it that this is saying look at the first 100 chars of that field correct?

Thanks for your help!

 

[SQLDenis]

yes only first 100

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/