Posted by Erland Sommarskog on 09/12/07 22:03

Emin (emin.shopper@gmail.com) writes:
> The really strange part is that if I left the clause "ORDER BY myDate"
> off the query I didn't get the error. My work around was to put the
> results of the query into a temporary table called #myTemp and then do
> "SELECT * FROM #myTemp ORDER BY myDate".
>
> 1. Is there a way to tell the query processor to use more resources so
> I don't get this error?

No. Possibly more memory to SQL Server could help.

> 2. Since it seems like the ORDER BY clause is causing the problem, is
> there a way to tell the query analyzer to just do the query and then
> order everything instead of trying to be too clever?

Yes, leave out the ORDER BY clause. If you leave it in, the query
processor has to comply. Or tell you that you can't. It can't produce
an unordered result when you requested an ordered one. That would
be a bug.

> 3. Is there a better way to solve this problem than using a temporaray
> table?

Without know nothing about the tables or the query, I cannot propose any
alternatives. A temp table seems like an easy way out. But it's possible
that the query itself could be simplified.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Back to original message]